Using COUNTIFS to count blank when cell has a formula

excel excel-2010
user3331363 picture user3331363 · Mar 12, 2014 · Viewed 39.8k times · Source

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.

Answer

barry houdini picture barry houdini · Mar 12, 2014

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

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Remove leading or trailing spaces in an entire column of data

How do I remove leading or trailing spaces of all cells in an entire column? The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

Read More
Insert picture into Excel cell

I'm tying to generate a report with pictures, but I cannot get the pictures into a single cell. I can get the pictures to "float" around my worksheet, but I need to put them into a cell. How can I …

Read More