Using COUNTIFS in Excel, check cells (containing formulas) that are empty

Question

Good day.

In it's basic form, I need to count how many cells are empty.

Using the following below, I can count how many cells are empty.

=COUNTIF(Sheet1!C:C,"<>")

However, if the cells in column C contain formulas, it won't work.

After some googling, I found out that using SUMPRODUCT will get what I need

=SUMPRODUCT(--(LEN(Sheet1!C:C)>0))

Now, here's my problem.

I need to use that as a criteria inside a COUNTIFS function, but I don't know how to do that because it's referencing some ranges.

So just to make it simple, using COUNTIF or COUNTIFS function specifically, how can I pass a criteria that checks if cell (with formula) is empty.

This formula returns 0 but most likely I'm just not passing it properly as a criteria.

=COUNTIF(Sheet1!C:C,SUMPRODUCT(--(LEN(Sheet1!C:C)>0)))

Happy for other ways to count cells (with formulas) which are empty, but I need to use it as a criteria for a COUNTIF/COUNTIFS function.

Thank you very much.

Answer 1

If I understand what you're looking for, you were pretty close already. Following the formula's you already found you can use them like: =COUNTIF(Sheet1!C:C,"<>")-SUMPRODUCT(--(LEN(Sheet1!C:C)>0)) It will result in the count of cells that have a value/formula minus the count of cells that show a value (blank formula result is excluded). The result is the count of cells that contain a formula with blank result.