CODEWITHSUNDEEP
excelexcel-formulaexcel-2007
L.P.
L.P.

Reputation: 83

IFCOUNTS and Not Numbers

I have a CountS formula

=COUNTIFS(Master!F:F,"<>"&"90",Master!B:B, "*Buildings*")

So I want to count all cells that contain "Building" in one column, EXCEPT if it contains the number 90 in column F

The thing is column F MUST be Text. So it contains Leters, Words, and (Text)Numbers. COUNTIFS does not seem to be able to pick up text-numbers, converting the whole column to numbers is not really an option.

How can I get CountS to see text-numbers?

Upvotes: 1

Views: 436

Answers (2)

barry houdini
barry houdini

Reputation: 46371

Depending on how much data you have it might be better to use a different approach, e.g. assuming data in rows 2 to 100 you could use this "array formula" for your revised description as per comments

=SUM(IF(ISNUMBER(SEARCH("buildings",Master!B2:B100)),IF(ISNA(MATCH(Master!F2:F100,{"Exp","70,"90"},0)),IF(Master!C2:C100<>"FH",1))))

confirmed with CTRL+SHIFT+ENTER

Upvotes: 2

SierraOscar
SierraOscar

Reputation: 17637

=COUNTIF(Master!B:B,"*Buildings*")-COUNTIFS(Master!F:F,"90",Master!B:B, "*Buildings*")

Upvotes: 2

Related Questions