Auto Filter with COUNTIF

**taichi56** · 09-20-2008, 06:39 PM

I have an Excel list of students. I am trying to find how many 9th grade students have a "y" in a certain field.

So I used autofilter and filter the list to give me just the 9th graders which is 703. Then I filtered the column with the "Y" and got 1414. The 1414 is wrong. That is the amount for the whole list, of 9th, 10th, 11th and 12th graders. Since I have only 703 9th graders the number should be less than 703. I used =COUNTIF(D1:D3000,"9") to give me the number of 9th graders and did the same for the "Y" column. What am I doing wrong? It is picking up all the records.

**Paul** · 09-20-2008, 07:06 PM

Hi taichi,

In Excel 2003 and earlier, COUNTIF can only accept one criteria. In your case you have two: one for 9th graders, the other for "Y".

You might try something like this instead:

=SUMPRODUCT(($D$1:$D$3000=9)*($E$1:$E$3000="Y"))

where column E contains your "Y" values (change to your actual column if necessary).

**Mark@Work** · 09-20-2008, 07:11 PM

There is a trick that can be used when you are trying to count based on multiple criteria.
In EXCEL TRUE is internally represented as 1, while FALSE is 0 (try typing in -TRUE and -FALSE).

Thus SUMPRODUCT(-(C2:C45=9),-(F2:F45="Y"))
gives the number of times a value in C2 to C45 is 9 AND the corresponding value in F2:F45 is "Y".

Mark.

**Paul** · 09-20-2008, 07:24 PM

Hi Mark,

Can't say I've seen a single '-' used in SUMPRODUCT before. Usually you see none, or a double unary '--'. I assume the double is preferred since the negation of the negation of a number is itself, while a single negation is the opposite of that number.

With two criteria negated once then multiplied, you should get correct results; but if you added a third you would actually get a negative count.

**taichi56** · 09-20-2008, 08:26 PM

Thank you pjoaquin but I am still getting weird results. I get 351 for a number for 9th graders, and the same for 10th graders. I know that both grades do not have the same amount of "Y" in the columns. What do think it is doing? Thank you for the quick replay.

**Paul** · 09-20-2008, 08:29 PM

How about posting the two formulas you're using for 9th and 10th graders?

I would expect them to be identical except for the 9 and 10, e.g.

=SUMPRODUCT((D1:D3000=9)*(E1:E3000="Y"))

and

=SUMPRODUCT((D1:D3000=10)*(E1:E3000="Y"))

**taichi56** · 09-21-2008, 03:15 PM

I apologize. I went back and looked at the code and realized that I had not changed the 9 to a 10 when I did the 10th graders. Thank you for your help. Everything worked great.