This seems like it should be easy but I don't know what I am missing.
Names Numbers Dave 12.00 John 14.30 Tom 11.40 John John 21.11 John 23.00
What I am after: Count of rows meeting these criteria:
name is john, numbers is not equal 23.00 : where "blank" should also be counted as "not equal to 23.00"
I tried this:
=COUNTIFS(A2:A7, "John", B2:B7, "<>23.00")
This outputs 2. If I put something in the blank line it changes to 3. So it's obviously not counting the blank as "not equal to 23.00" when I want it to do so.
I also tried:
=COUNTIFS(A2:A7, "John", B2:B7, "<>23.00", B2:B7, "")
And this outputs 0, I guess it is "AND"ing the criteria and not "OR"ing ?
How can I accomplish this?
'Count if Name Equals John, and the Numbers is ANYTHING BUT 23.00 including blanks'
Note that this needs to be expandable, for example if I added a "City" column I might need to be able to count:
'Count if Name Equals John, and City equals Denver, and the Numbers is NOT 23.00'
'Count if Name Equals John, and City IS NOT Denver, and numbers IS NOT 23.00'
And again, a blank value in City should be counted as "Not Denver'.
and so on.
What am I missing here?