Using COUNTIF with empty range criteria with variable ranges

Question

I have a weird problem trying to use COUNTIF .

pretend for a moment, this is the sheet in question:

 AB John Doe John Smith John 

The last value (B3) is an empty field.

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

The only way I was able to do this was to explicitly specify the range to be counted ( B1:B3 ), but this formula will do this on multiple sheets that do not have all the same number of rows so I cannot use COUNTBLANK because it returns stunningly high results if I just name column B name and specify the name as a range.

---

EDIT:

So apparently countif cannot be used for this? The workaround I found is using SUMPRODUCT . Is this the best way to do this?

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

Answer 1

You can use COUNTIFS for several criteria. For example, you can use:

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

Answer 2

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

This is an array formula: use Ctrl + Shift + Return as soon as you finish editing, not just Return.

The trick is to use multiplication as a replacement for the AND function as AND to fail if you mix array string comparisons with ISBLANK.

Answer 3

You can use something like this:

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

It calculates the difference between empty cells in column B and empty cells in column A.