11

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.

user3331363
  • 305
  • 1
  • 4
  • 13

1 Answers1

24

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • this is going in a reverse counter. Say I have the formula from B1:B10. I copied your formula in cell C3. It display 10. Then whenever data comes in B1, cell C3 display 9 – user3331363 Mar 12 '14 at 20:15
  • 1
    so you only want populated values, what sort of values? If they are text values try `=COUNTIF(B:B,"*?")` - that will count text values but not "" – barry houdini Mar 12 '14 at 20:26
  • say that B1 has the formula =IF(A1="","",A1), Then it should count only when the formula return a value not when its blank – user3331363 Mar 12 '14 at 20:29
  • 1
    I know that you have "" values but I meant what are the values of the populated cells? There are various options depending on whether those are text, numbers or a combination - I edited my answer accordingly – barry houdini Mar 12 '14 at 20:31
  • 1
    True dates are numbers so you can use COUNT to count those - within a COUNTIF function use COUNTIFS(B:B,">0") - that should exclude blanks – barry houdini Mar 12 '14 at 20:37