Google Sheets - COUNTIFS with some blank values and multiple criteria

Question

This seems like it should be easy but I don't know what I am missing.

Example Data:

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?

Answer 1

The criteria in countifs() are always combined with "and".

To more easily control those criteria, use filter(), like this:

=counta(iferror(filter(A2:A7, A2:A7 = "John", B2:B7 <> 23)))

See filter().

Answer 2

Formula

=SUMPRODUCT(A2:A7="John", B2:B7<>23)

Explanation

SUMPRODUCT calculates the sum of the products of corresponding entries in 2 equally sized arrays or ranges.

SUMPRODUCT(array1, [array2, ...])

To illustrate SUMPRODUCT's behavior, I've combined SUM, ARRAYFORMULA, and PRODUCT:

=SUMPRODUCT(A2:A7="John", B2:B7<>23)
=SUM(ARRAYFORMULA(PRODUCT(A2:A7="John", B2:B7<>23)))
=SUM(ARRAYFORMULA(PRODUCT({0; 1; 0; 1; 1; 1}, {1; 1; 1; 1; 1; 0})))
=SUM({0*1; 1*1; 0*1; 1*1; 1*1; 1*0}) 
=SUM({0; 1; 0; 1; 1; 0}) 
=3