count question

[rkrause]

Hello i have an excell sheet where the user enters in an 'x' in a cell and at the bottom of hte sheet it counts up the 'x' entered. here is my formula.

=COUNTIF(F6:F74,"x")

my question is, how would i do it if the user wanted to enter multipe 'x' into the cell and still count multiple 'x' but also just a single 'x'

thanks

 

[Alansidman]

If you wish to count multiple x in a cell, ie. xx,x,xxx, etc then in your formula, it would read =countif(yourrange, "x*")

Alan

 

[rkrause]

that didnt work. if i put xxx in a cell, it still counted them as 1, where i want it it to count all 3 of the x's


thanks

 

[Alansidman]

I misunderstood you. I thought that you wanted to count it as 1 regardless of the number of x's in a cell. I am thinking that you may have to apply the Len function to an adjacent cells to the data cells and then do a sum on the values in the Len column.

See attached

 

[NBVC]

You can use SUMPRODUCT.

If all you have is x's in those cells, then:

=SUMPRODUCT(--(LEN(F6:F74)))

if you may have other characters in some of those cells, but only want to count the "x', then:

=SUMPRODUCT(--(LEN(F6:F74)-LEN(SUBSTITUTE(F6:F74,"x",""))))