77

How do I get countifs to select all non-blank cells? I have two other criteria, so using counta alone is not an option.

Using istext makes the result give 0, always. Excel gives an error when using <>"" as a criterion.

Ninjakannon
  • 3,751
  • 7
  • 53
  • 76
Kim
  • 2,747
  • 7
  • 41
  • 50

7 Answers7

112

Use a criteria of "<>". It will count anything which isn't an empty cell, including #NAME? or #DIV/0!. As to why it works, damned if I know, but Excel seems to understand it.

Note: works nicely in Google Spreadsheet too

Dheeraj Bhaskar
  • 18,633
  • 9
  • 63
  • 66
Pesto
  • 23,810
  • 2
  • 71
  • 76
  • I tried that myself and found out it works, but as you also point out - it makes no sense why excel accepts it. I had to add another criterion to avoid the 0's that my vlookups show. – Kim Apr 21 '09 at 12:25
  • 2
    Wow! This answer is still a lifesaver 7 years later - and contains information which I can't find in the Microsoft docs. Thanks!! – AAT May 11 '16 at 09:29
  • 11
    Note that this does not work for formulas that evaluate to empty strings. For example, it will count a cell containing `=""` as non-blank. – elixenide Apr 11 '17 at 18:58
27

If multiple criteria use countifs

=countifs(A1:A10,">""",B1:B10,">""")

The " >"" " looks at the greater than being empty. This formula looks for two criteria and neither column can be empty on the same row for it to count. If just counting one column do this with the one criteria (i.e. Use everything before B1:B10 not including the comma)

jcern
  • 7,798
  • 4
  • 39
  • 47
Jason
  • 279
  • 3
  • 2
  • This is helpful as there are cases where an apparently empty cell will not be excluded by `"<>"`, but will when using `">"""`. – Ryan D.W. Jan 15 '16 at 17:34
  • 4
    The ">""" solution is perfect. The problem with "<>" is that it counts cells that have a formula in them even if it results in a blank value (like if you use IFERROR, for example), whereas ">""" seems to work for the computed value of the cell instead of the contents. – JoeP Sep 16 '16 at 14:31
  • This is exactly what I have been looking for. "<>" did not work for me. This works very well. Thank you. – Park Apr 23 '20 at 06:03
8

In Excel 2010, You have the countifS function.

I was having issues if I was trying to count the number of cells in a range that have a non0 value.

e.g. If you had a worksheet that in the range A1:A10 had values 1, 0, 2, 3, 0 and you wanted the answer 3.

The normal function =COUNTIF(A1:A10,"<>0") would give you 8 as it is counting the blank cells as 0s.

My solution to this is to use the COUNTIFS function with the same range but multiple criteria e.g.

=COUNTIFS(A1:A10,"<>0",A1:A10,"<>")

This effectively checks if the range is non 0 and is non blank.

barbz
  • 81
  • 1
  • 1
6

If you are using multiple criteria, and want to count the number of non-blank cells in a particular column, you probably want to look at DCOUNTA.

e.g

  A   B   C   D  E   F   G
1 Dog Cat Cow    Dog Cat
2 x   1          x   1
3 x   2 
4 x   1   nb     Result:
5 x   2   nb     1

Formula in E5: =DCOUNTA(A1:C5,"Cow",E1:F2)

tardate
  • 16,424
  • 15
  • 50
  • 50
  • 1
    This is a much better option than the accepted answer. Why you'd use a Vlookup with IF's is beyond me when you want to count. Dcount is your friend. Still if you like large chunky formulas use the other answer. – Anonymous Type Jan 21 '10 at 00:19
4

You can try this : 

=COUNTIF(Data!A2:A300,"<>"&"")
Smern
  • 18,746
  • 21
  • 72
  • 90
Vinh Tran
  • 41
  • 1
3

I find that the best way to do this is to use SUMPRODUCT instead:

=SUMPRODUCT((A1:A10<>"")*1)

It's also pretty great if you want to throw in more criteria:

=SUMPRODUCT((A1:A10<>"")*(A1:A10>$B$1)*(A1:A10<=$B$2))
ikh
  • 2,336
  • 2
  • 19
  • 28
0

The best way I've found is to use a combination "IF" and "ISERROR" statement:

=IF(ISERROR(COUNTIF(E5:E356,1)),"---",COUNTIF(E5:E356,1)

This formula will either fill the cell with three dashes (---) if there would be an error (if there is no data in the cells to count/average/etc), or with the count (if there was data in the cells)

The nice part about this logical query is that it will exclude entirely blank rows/columns by making them textual values of "---", so if you have a row counting (or averaging), which was then counted (or averaged) in another spot in your formula, the second formula won't respond with an error because it will ignore the "---" cell.

Kay
  • 1