CODEWITHSUNDEEP
excelsearchformulacountif
Felipe Wagner
Felipe Wagner

Reputation: 148

Excel Countifs - Search Text in String

I have a column (let's say B1) in an Excel form that shows some products like this:

Sand Systems
Gas Systems
Blenders
Other
Other 2
Other 3
Other 4

I need to count the rows that doesn't contain the text "Other #" (# being a number), in other words, "Sand Systems", "Gas Systems", "Blenders" and "Other" = 4.

The solution so far was using an auxiliary column with the formula =SEARCH , but how to make it ignore the "Other" cell and accept "Other #"? Additionally, I wouldn't like to use an auxiliary column, but a cell with the =COUNTIF formula. Any advice?

Upvotes: 0

Views: 1119

Answers (2)

sous2817
sous2817

Reputation: 3960

One approach is something like:

=COUNTIF(A1:A7,"<>" & "Other *")

This returns 4, which is the expected result?

Using the whole column reference will return the incorrect result, just FYI. If you're dead set on using whole column references use Ron Rosenfeld's formula:

=COUNTA(A:A)-COUNTIF(A:A,"Other *")

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

=COUNTA(A:A)-COUNTIF(A:A,"Other *")

Count them all, then subtract the one's that contain Other followed by space and anything else.

Upvotes: 1

Related Questions