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

Question

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   lt  gt     as a criterion

User · Answer

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   lt  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   lt  0  A1 A10   lt      This effectively checks if the range is non 0 and is non  blank

User · Answer

Use a criteria of   lt  gt     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

User · Answer

You can try this      COUNTIF Data A2 A300   lt  gt   amp

User · Answer

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

User · Answer

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

User · Answer

I find that the best way to do this is to use SUMPRODUCT instead    SUMPRODUCT  A1 A10 lt  gt     1    It s also pretty great if you want to throw in more criteria    SUMPRODUCT  A1 A10 lt  gt      A1 A10 gt  B 1   A1 A10 lt   B 2

User · Answer

If multiple criteria use countifs   countifs A1 A10   gt     B1 B10   gt        The     gt           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

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

Examples related to excel