Countif on horizontal data (in a row)

[Gazzat]

Hi,
Please see the sheet below and the formula that has returned a result of "0" in cell K5.
The image represents a small sample of a 25,000 row spreadsheet. For each row, I would like column K to count the number of cells from B to J in the same row (in this case row 5) that begin with a "K". The result in K5 should read "3", not "0".

Please help- 6 hours of trying different solutions and searching the boards has not lead to an answer yet
Thanks!!!
Gazzat

 

[Toadstool]

Hi Gazzat,

It appears the cells have some other character (maybe a space?) before the "K".

You can use =CODE(cell) to check what that first character is. 32 would be a space.

If you don't care about that character you can use =COUNTIF(B5:H5,"?K*") to wildcard around it.

 

[6StringJazzer]

I suspect the problem is in your data. Could you have leading spaces in those strings? Try this first:

=COUNTIF(B5:J5," K*")

then if that doesn't work try this one

=COUNTIF(B5:J5,"*K*")

If either of those work, you have a leading space or other unprintable character in the beginning of those strings.

 

[6StringJazzer]

Looking at your screenshot I am pretty sure you have leading spaces in your data except for column B.

 

[Gazzat]

Looking at your screenshot I am pretty sure you have leading spaces in your data except for column B.

Dear Jeff,
Your rapid response was a lifesaver , displaying Wes Montgomeryesque fluidity and precision. =COUNTIF(B5:J5,"*K*") was the winner.
Thank you so much,
Gaz

 

[Gazzat]

Hi Gazzat,

It appears the cells have some other character (maybe a space?) before the "K".

You can use =CODE(cell) to check what that first character is. 32 would be a space.

If you don't care about that character you can use =COUNTIF(B5:H5,"?K*") to wildcard around it.

Thanks Toadstool!!- =COUNTIF(B5:H5,"*K*") worked.

 

[6StringJazzer]

displaying Wes Montgomeryesque fluidity and precision

Praise of which I am not worthy