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,"")

Answer 2

You can use something like this:

=COUNTBLANK(B2:B100000)-COUNTBLANK(A2:A100000)

It calcaulates the diference between the empty cells in column B and the empty cells in column A.

Answer 3

Use =SUM(IF(A1:A3="John",1,0)*IF(ISBLANK(B1:B3),1,0))

This is an array formula: use Ctrl + Shift + Return once you've finished editing rather than just Return.

The trick is to use a multiplication as a replacement to an AND function as AND fails if you mix array string comparisions with ISBLANK.