excel - using COUNTIF with blank value criteria with variable ranges

Question

I have sort of a bizarre issue here attempting to use COUNTIF.

pretend for a moment this is the sheet in question:

A        B
John     Doe
John     Smith
John

The last value there (B3) is an empty field.

The intended COUNTIF formula should count the number of empty values in the B column only IF John is present in the A column.

The only way I have been able to do this successfully is explicitly specifying the range to be counted (B1:B3), but this formula is going to be doing this on multiple sheets that do not all have the same number of rows, therefore, I cannot use COUNTBLANK because it is returning staggeringly high results if I simply name the B column a name and specify the name as the range.

---

EDIT:

So apparently countif cannot be used for that? A workaround I have found is using SUMPRODUCT. Is this the best way to go about doing this?

=SUMPRODUCT((September!K1:K16000="John")*(September!L1:L16000=""))

Answer 1

You can use COUNTIFS for multiple criteria. For instance, you can use:

=COUNTIFS(A:A,"John",B:B,"")