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'm using Excel and having some problems with Countif and I don't understand how it works differently from SubTotal. I used the GUI to subtotal stuff and all the subtotals are right. Then I attempted to use the Countif to see how many requirements passed. That worked for the first subtotal only. It's easy to see why. When I look at the box for the subtotal, it says:

=SUBTOTAL(3,C286:C292)

When I look at my formula for passed requirements, I have:

=IF(ISTEXT(A285),COUNTIF(C286:C338,"=Passed"),"")

Notice that the last column is wrong. How did the Subtotal manage to keep this correct? I typed in the formula for passed requirements and dragged it down the page. Everything behaved as expected (even the bit about ISTEXT dutifully figured out which row was which), but it got the last row wrong.

Any ideas?

SRS Maintenance Count   7   44  
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Maintenance Passed              
SRS Reports Count   12  43  
SRS Reports Passed              
SRS Reports Passed              
SRS Reports Passed              
SRS Reports Passed              
SRS Reports Failed              
SRS Reports Passed              
SRS Reports Passed              
SRS Reports Failed              
SRS Reports Passed              
SRS Reports Passed              
SRS Reports Failed              
share|improve this question

migrated from stackoverflow.com Jun 10 '10 at 16:31

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

1 Answer 1

up vote 0 down vote accepted

This SubTotal() function is really just using the CountA() function (specified by the "3" that you passed into it) to count the specified range. This function will count any cell that isn't empty (regardless what it contains). The only reason it gets the total right is because all of those entries happen to be "Passed". Try changing one of the values to "SRS Maintenance Failed" and you will see the total doesn't change.

There is a problem with your If() statement. You don't actually need to use the If() together with the CountIf() statement. The logic of the CountIf() statement is: "If the cell matches the value I've specified, then count it". So the following statement will work instead.

=CountIf(C286:C292, "SRS Maintenance Passed")

The problem with the CountIf() statement is that you can only specify a single term to match. So you would need two separate 'subtotals' to get the value of both Reports that passed and Maintenance that passed.

=(CountIf(C286:C292, "SRS Maintenance Passed") + (CountIf(C286:C292, "SRS Reports Passed"))
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.