up vote 0 down vote favorite
share [g+] share [fb]

Possible Duplicate:
COUNTIFS does not seem to count blank cells

I am trying to build a COUNTIF statement in Excel 2010 using a total of 4 sets of criteria. I would ideally like it to look something like this:

COUNTIFS(range1,value in a1,range2,value in b1,range3,value in c1,range4,value in d1)

This works fine if there are actual values in cells A1-D1. however, if one of those cells is blank, I don't get the number back that I would expect. (I am testing this by autofiltering using the criteria I'm setting in A1-D1). What DOES seem to be happening is that if C1 is blank, then the COUNTIFS returns a count of all rows where C1 is not blank.

Is there a way to set a default so that if there is no value in A1-D1, COUNTIFS includes everything?

link|improve this question
0% accept rate
feedback

migrated from stackoverflow.com Sep 10 '11 at 6:36

This question came from our site for professional and enthusiast programmers.

closed as exact duplicate by Linker3000, studiohack♦ Sep 10 '11 at 8:33

This question covers exactly the same ground as earlier questions on this topic; its answers may be merged with another identical question. See the FAQ for guidance on how to improve it.

1 Answer

up vote 0 down vote

If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.

If you are trying to match empty cells, put a single quote ' in the criteria cell (displays as a blank cell) This will match actual blank cells

link|improve this answer
feedback

Not the answer you're looking for? Browse other questions tagged or ask your own question.