excelexcel-2010

Using COUNTIFS to count blank when cell has a formula

I have criteria where I need to count if a column B is not blank. But I have a formula in Cell B, So if I do a simple

=Countifs(B1:B10,"<>")

This returns the count of the cells which have the formula but I just need the blanks when the formula does not populate anything.

Solution

  • Try this formula

    [edited as per comments]

    To count populated cells but not "" use

    =COUNTIF(B:B,"*?")

    That counts text values, for numbers

    =COUNT(B:B)

    If you have text and numbers combine the two

    =COUNTIF(B:B,"*?")+COUNT(B:B)

    or with SUMPRODUCT - the opposite of my original suggestion

    =SUMPRODUCT((B:B<>"")*(B:B<>0))