Counting Cells which Contain Specific Text Strings 2

[jakatz]

I know there's a way of doing this, I just can't think of it at the moment and need a quick answer. Here is what I am looking to do:

I have a list of part numbers (4,000 in total), of which I have specific parts I need to identify and count. I need a formula which will return to total count of cells which contain 'SVBL' (regardless of where the 'SVBL' is in the text). An example of the text (note the text length is variable) I might be seeing is:

Cell A1: 12345SVBL-45567
Cell A2: 11546889SVBL
Cell A3: 7576-0001-SVBL
Cell A4: 9864SVBL
Cell A5: 98-SVBL-S322
Cell A6: 7568-001-SCBL (this should not be counted)

Anyone have any ideas on how I can do this. I tried doing a "Count" with a nested "Find" and setting it as an array formula, but I couldn't get that to work quite right.

 

[Zathras]

Here is an array formula that should work for you:
[tt]
=SUM(IF(ISERROR(SEARCH("svbl",A2:A7000)),0,1))
[/tt]
(Use Ctrl+Shift+Enter to enter an array formula.)

Set the range as needed for your situation.

 

[Bowers74]

Or you could try a non-array formula using the SUMPRODUCT function: ;-)

=SUMPRODUCT((NOT(ISERROR(FIND("SVBL",A2:A7000)>0)))*1)



Peace!!

Mike

Didn't get the answers that you wanted? Then . . . Click Here