Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

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

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

marked as duplicate by Linker3000, studiohack Sep 10 '11 at 8:33

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

add comment

1 Answer 1

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

share|improve this answer
add comment

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