How do I get countifs to select all non-blank cells in Excel?

小开

最佳答案

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

小开

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)

小开

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.

小开

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)

小开

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.

小开

You can try this :

=COUNTIF(Data!A2:A300,"<>"&"")

小开

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