Will the COUNTIF function work with auto-filtering?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a large amount of student demographic, course, and class data on an
Excel worksheet for an entire school district. The ethnic code for each
student is a single alpha character. I need to find a way to get a total for
each ethnic code in each course when filtering by each course.
Each column of data has a header and I am using the Auto-filter feature for
filtering. Is there a way that the COUNTIF function can be set up for each
ethnic code so that it will count from the filtered data? If not, is there
any other function, or nested functions, that will work?
Thanks,
RC
 
Lookup help on the SUBTOTAL funciton. It can be set to include/exclude
filtered data. That should accomplish what you're looking for.

HTH,
Elkar
 
Thanks Elkar, do you have more details on how to get the SUBTOTAL function to
include or exclude filtered data? It seems to be limited to the function
numbers listed in the "Help on this function".
I'm using the COUNTA function (#3) nested in the SUBTOTAL function to get a
count of all the students in a course when I filter by course number, yet if
I can set up more SUBTOTAL functions to include or exclude data like you
mentioned then that'll be just waht is needed for funding the subtotals for
each ethnic code in the filtered data.
Thanks again,
RC
 
I'm not sure I understand what else you're looking for? You shouldn't have
to "nest" any functions to get a count. Use the codes 100-111 instead of
1-11 to exclude filtered data.

=SUBTOTAL(103,A2:A100) will give you a count of all visible data within that
range. Any data that has been filtered out, will not be counted.

Does that help?
Elkar
 
Assuming that Column A contains the 'Ethnic Code', try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
00=D2))

....where D2 contains the 'Ethnic Code' of interest. Adjust the range
accordingly.

Hope this helps!
 
Thanks again Elkar, and maybe the following will give a better explanation.
There are six ethnic codes (A, B, H, M, I, W) used in the student
demographics part of the data to denote student's ethnicity. And, when I
filter the data in by course number, or by class, there could be anywhere
from 2 to 50 students in that class. For example lets say that a class has
been filtered and the class contains 27 students, and of those students there
are 2 Asian (A)students, 11 Black (B) students, 3 Hispanic (H) students, 1
Multi-racial (M) student, 2 Native American (I) students, and 8 White (W)
students. The next class filtered may have 30 students and the subtotals for
each ethnic code will be all different. I was hoping that maybe there was a
way of setting up a function for each ethnic code where each one will return
a subtotal for a specific ethnic code in a class or course when filtering
only by the class or course number so that the class or course can be printed
out with the subtotals for each ethnic code in that class or course displayed
on the printout.
Thank you again for your help,
RC
 
Thanks Domenic, this works fine.
RC

Domenic said:
Assuming that Column A contains the 'Ethnic Code', try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
00=D2))

....where D2 contains the 'Ethnic Code' of interest. Adjust the range
accordingly.

Hope this helps!
Click to expand...
 
Can that function, which I use often in dasnboards, be modified to count two
factors? Among a set of filtered rows, I want to count those with "Low" in
one column AND "High" in another column.

Thanks in advance.
 
Just add another condition

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),
--(A2:A100="High"),--(C2:C100="Low"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
So it's okay that the references inside the Subtotal function only address
the one column (in this case, A)? Awesome, thanks.
 
No it doesn't matter, it can be any column. The only thing to beware of is
that an empty cell will return the same value as a non-visible cell, so it
would not be counted even if other criteria were met.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Back
Top