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.

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
    
I'm not sure exactly what you are running into. Maybe it has something to do with COUNTIFS defaulting the C1 condition to 0 if it is blank, and maybe your non-blank rows had zeros in them? –  Mafu Josh Sep 10 '11 at 2:53
1  
According to the COUNTIFS and COUNTIF documentation, it looks like you can count non-blanks by using * instead of an empty cell. (On the COUNTIF page, see Example 2, Formula 3.) I don't have Excel at home to confirm this works for your example. –  Bavi_H Sep 10 '11 at 3:47
    
Sorry, perhaps I can try to clarify some more based on Kaze's excellent picture above. I am trying to build a single COUNTIFS statement that will accept user selections from cells A2, B2, and/or C2. If the user does NOT select a value in one of the cells, I need that selection to default to a SELECT ALL (asterisk) equivalent for that variable. If the user does select one or more parameters in cells A2-C2, then the COUNTIFS should use that value. For some reason, this does not seem to be happening. –  Jay Sep 12 '11 at 14:50
    
Is there a difference between what you are doing and what Kaze did? What do you mean by "user selection"? –  Mafu Josh Sep 13 '11 at 0:24

2 Answers 2

The DCOUNT function may work as an alternative.

You have to use a criteria range that includes its own labels, but blank criteria values mean "include all".

Note: In the pictures below, the blank cells have nothing in them, as if you clicked on the cell and pressed Delete.

Setting up the DCOUNT function

Example of DCOUNT in use

share|improve this answer
    
The problem with using the "" approach is that I can't seem to build a single statement that works when a particular column is blank vs. non-blank. For example, in the picture above, the ="" approach works fine if I always assume that c2 is going to be null. but if C2 can be either null or some value, then it doesn't work. –  Jay Sep 16 '11 at 22:09
    
(Edited answer to clarify:) In the pictures above, the blank cells have nothing in them, as if you clicked on the cell and pressed Delete. Cells with empty text values, like ' or ="", are not used in above pictures. Does using blank cells work for you? –  Bavi_H Sep 17 '11 at 2:04
    
+1 After reading Jay's clarifications, I deleted my answer in favor of this one. DCOUNT would be better than COUNTIFS. –  Ellesa Sep 17 '11 at 9:58

As you know, using Excel's filter will let you choose to show all items including blanks, show only blank items, or show any other combination of items. However, it will hide the unchosen items. If hiding the unchosen items is acceptable, the solution below may work for you.

You can count the rows chosen by a filter with the SUBTOTAL function as follows.

If any of the cells in your data are blank, setup your data with a dummy column as shown below. The dummy column has text in every row so we have a reliable way to count any row.

Setting up the dummy column values and SUBTOTAL function

Use the SUBTOTAL function with function_num 103 and selecting the dummy value cells for the range. function_num 103 counts only rows chosen by the filter.1

Click on a cell in your data area and turn on the filter. Select the entire dummy column (click on the cloumn D header), right-click and choose Hide.

Turning on the filter and hiding the dummy column

Now when you change the items in the filter, the count will indicate the number the rows chosen by the filter.

Filter in use, count indicates number of rows chosen by filter


  1. I just noticed the Remarks at the bottom of the SUBTOTAL documentation say it always ignores rows not included in the result of a filter, so you can use either function_num 3 or 103 to count the dummy values.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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