Count If?

Originally Posted by OlYeller21

I tried using SEARCH on every cell that I want to count with wildcards up to the point where the "M1" would be. It counts the cells that do have "M1" fine but if the cell doesn't has "H1" (the only other option), "3VALUE1" appears as the answer. I thought if the string wasn't found that it would return a 0 which wouldn't change the total but I guess not.
Zach

There may be an easier way, but it can be done with a few helper columns

Assume that you have data in A, B and C, and that columns D (to say N) onwards are free, in d1 put

=10-COUNTBLANK(E1:N1)

and in E1 put

=IF(ISERROR(FIND("m1",A1)),"",MID(A1,FIND("m1",A1)+1,999))

in F1 put

=IF(ISERROR(FIND("m1",E1)),"",MID(E1,FIND("m1",E1)+1,999))

and formula-fill that to the right for as many cells as you expect occurrances

Bulk-Formula-fill D1:N1 down as far as required

hope this gives a clue . . .
---

note, you can do this on a second sheet, drag the data to that sheet with =Sheet1!a1 in column A, and back on Sheet1 put =Sheet2!D1 to retrieve the answer.