# Neophyte question about using Subtotal and CountIf in Excel

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
``````
-

## migrated from stackoverflow.comJun 10 '10 at 16:31

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

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"))