How do I get countifs to select all non-blank cells? I have two other criterions, so using counta alone is not an option.
Using istext makes the result give 0, always. Excel gives an error when using <>"" as criteria.
+3
A:
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.
Pesto
2009-04-21 12:13:30
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
2009-04-21 12:25:44
A:
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
2009-04-21 12:20:45
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
2010-01-21 00:19:07