software

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

quot;RCquot; wrote:

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

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 quot;Help on this functionquot;.
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

quot;Elkarquot; wrote:

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

I'm not sure I understand what else you're looking for? You shouldn't have
to quot;nestquot; 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

quot;RCquot; wrote:

gt; Thanks Elkar, do you have more details on how to get the SUBTOTAL function to
gt; include or exclude filtered data? It seems to be limited to the function
gt; numbers listed in the quot;Help on this functionquot;.
gt; I'm using the COUNTA function (#3) nested in the SUBTOTAL function to get a
gt; count of all the students in a course when I filter by course number, yet if
gt; I can set up more SUBTOTAL functions to include or exclude data like you
gt; mentioned then that'll be just waht is needed for funding the subtotals for
gt; each ethnic code in the filtered data.
gt; Thanks again,
gt; RC
gt;
gt; quot;Elkarquot; wrote:
gt;
gt; gt; Lookup help on the SUBTOTAL funciton. It can be set to include/exclude
gt; gt; filtered data. That should accomplish what you're looking for.
gt; gt;
gt; gt; HTH,
gt; gt; Elkar
gt; gt;
gt; gt; quot;RCquot; wrote:
gt; gt;
gt; gt; gt; I have a large amount of student demographic, course, and class data on an
gt; gt; gt; Excel worksheet for an entire school district. The ethnic code for each
gt; gt; gt; student is a single alpha character. I need to find a way to get a total for
gt; gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; gt; Each column of data has a header and I am using the Auto-filter feature for
gt; gt; gt; filtering. Is there a way that the COUNTIF function can be set up for each
gt; gt; gt; ethnic code so that it will count from the filtered data? If not, is there
gt; gt; gt; any other function, or nested functions, that will work?
gt; gt; gt; Thanks,
gt; gt; gt; RC

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!

In article gt;,
RC gt; wrote:

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

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
quot;Elkarquot; wrote:

gt; I'm not sure I understand what else you're looking for? You shouldn't have
gt; to quot;nestquot; any functions to get a count. Use the codes 100-111 instead of
gt; 1-11 to exclude filtered data.
gt;
gt; =SUBTOTAL(103,A2:A100) will give you a count of all visible data within that
gt; range. Any data that has been filtered out, will not be counted.
gt;
gt; Does that help?
gt; Elkar
gt;
gt; quot;RCquot; wrote:
gt;
gt; gt; Thanks Elkar, do you have more details on how to get the SUBTOTAL function to
gt; gt; include or exclude filtered data? It seems to be limited to the function
gt; gt; numbers listed in the quot;Help on this functionquot;.
gt; gt; I'm using the COUNTA function (#3) nested in the SUBTOTAL function to get a
gt; gt; count of all the students in a course when I filter by course number, yet if
gt; gt; I can set up more SUBTOTAL functions to include or exclude data like you
gt; gt; mentioned then that'll be just waht is needed for funding the subtotals for
gt; gt; each ethnic code in the filtered data.
gt; gt; Thanks again,
gt; gt; RC
gt; gt;
gt; gt; quot;Elkarquot; wrote:
gt; gt;
gt; gt; gt; Lookup help on the SUBTOTAL funciton. It can be set to include/exclude
gt; gt; gt; filtered data. That should accomplish what you're looking for.
gt; gt; gt;
gt; gt; gt; HTH,
gt; gt; gt; Elkar
gt; gt; gt;
gt; gt; gt; quot;RCquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have a large amount of student demographic, course, and class data on an
gt; gt; gt; gt; Excel worksheet for an entire school district. The ethnic code for each
gt; gt; gt; gt; student is a single alpha character. I need to find a way to get a total for
gt; gt; gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; gt; gt; Each column of data has a header and I am using the Auto-filter feature for
gt; gt; gt; gt; filtering. Is there a way that the COUNTIF function can be set up for each
gt; gt; gt; gt; ethnic code so that it will count from the filtered data? If not, is there
gt; gt; gt; gt; any other function, or nested functions, that will work?
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; RC

Thanks Domenic, this works fine.
RC

quot;Domenicquot; wrote:

gt; Assuming that Column A contains the 'Ethnic Code', try...
gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
gt; 00=D2))
gt;
gt; ....where D2 contains the 'Ethnic Code' of interest. Adjust the range
gt; accordingly.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; RC gt; wrote:
gt;
gt; gt; I have a large amount of student demographic, course, and class data on an
gt; gt; Excel worksheet for an entire school district. The ethnic code for each
gt; gt; student is a single alpha character. I need to find a way to get a total for
gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; Each column of data has a header and I am using the Auto-filter feature for
gt; gt; filtering. Is there a way that the COUNTIF function can be set up for each
gt; gt; ethnic code so that it will count from the filtered data? If not, is there
gt; gt; any other function, or nested functions, that will work?
gt; gt; Thanks,
gt; gt; RC
gt;

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 quot;Lowquot; in
one column AND quot;Highquot; in another column.

Thanks in advance.

quot;Domenicquot; wrote:

gt; Assuming that Column A contains the 'Ethnic Code', try...
gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
gt; 00=D2))
gt;
gt; ....where D2 contains the 'Ethnic Code' of interest. Adjust the range
gt; accordingly.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; RC gt; wrote:
gt;
gt; gt; I have a large amount of student demographic, course, and class data on an
gt; gt; Excel worksheet for an entire school district. The ethnic code for each
gt; gt; student is a single alpha character. I need to find a way to get a total for
gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; Each column of data has a header and I am using the Auto-filter feature for
gt; gt; filtering. Is there a way that the COUNTIF function can be set up for each
gt; gt; ethnic code so that it will count from the filtered data? If not, is there
gt; gt; any other function, or nested functions, that will work?
gt; gt; Thanks,
gt; gt; RC
gt;

Just add another condition

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),
--(A2:A100=quot;Highquot;),--(C2:C100=quot;Lowquot;))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;andy62quot; gt; wrote in message
...
gt; Can that function, which I use often in dasnboards, be modified to count
two
gt; factors? Among a set of filtered rows, I want to count those with quot;Lowquot;
in
gt; one column AND quot;Highquot; in another column.
gt;
gt; Thanks in advance.
gt;
gt; quot;Domenicquot; wrote:
gt;
gt; gt; Assuming that Column A contains the 'Ethnic Code', try...
gt; gt;
gt; gt;
=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
gt; gt; 00=D2))
gt; gt;
gt; gt; ....where D2 contains the 'Ethnic Code' of interest. Adjust the range
gt; gt; accordingly.
gt; gt;
gt; gt; Hope this helps!
gt; gt;
gt; gt; In article gt;,
gt; gt; RC gt; wrote:
gt; gt;
gt; gt; gt; I have a large amount of student demographic, course, and class data
on an
gt; gt; gt; Excel worksheet for an entire school district. The ethnic code for
each
gt; gt; gt; student is a single alpha character. I need to find a way to get a
total for
gt; gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; gt; Each column of data has a header and I am using the Auto-filter
feature for
gt; gt; gt; filtering. Is there a way that the COUNTIF function can be set up for
each
gt; gt; gt; ethnic code so that it will count from the filtered data? If not, is
there
gt; gt; gt; any other function, or nested functions, that will work?
gt; gt; gt; Thanks,
gt; gt; gt; RC
gt; gt;
So it's okay that the references inside the Subtotal function only address
the one column (in this case, A)? Awesome, thanks.

quot;Bob Phillipsquot; wrote:

gt; Just add another condition
gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),
gt; --(A2:A100=quot;Highquot;),--(C2:C100=quot;Lowquot;))
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;andy62quot; gt; wrote in message
gt; ...
gt; gt; Can that function, which I use often in dasnboards, be modified to count
gt; two
gt; gt; factors? Among a set of filtered rows, I want to count those with quot;Lowquot;
gt; in
gt; gt; one column AND quot;Highquot; in another column.
gt; gt;
gt; gt; Thanks in advance.
gt; gt;
gt; gt; quot;Domenicquot; wrote:
gt; gt;
gt; gt; gt; Assuming that Column A contains the 'Ethnic Code', try...
gt; gt; gt;
gt; gt; gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A100,ROW(A2:A100)-ROW(A2),0,1)),--(A2:A1
gt; gt; gt; 00=D2))
gt; gt; gt;
gt; gt; gt; ....where D2 contains the 'Ethnic Code' of interest. Adjust the range
gt; gt; gt; accordingly.
gt; gt; gt;
gt; gt; gt; Hope this helps!
gt; gt; gt;
gt; gt; gt; In article gt;,
gt; gt; gt; RC gt; wrote:
gt; gt; gt;
gt; gt; gt; gt; I have a large amount of student demographic, course, and class data
gt; on an
gt; gt; gt; gt; Excel worksheet for an entire school district. The ethnic code for
gt; each
gt; gt; gt; gt; student is a single alpha character. I need to find a way to get a
gt; total for
gt; gt; gt; gt; each ethnic code in each course when filtering by each course.
gt; gt; gt; gt; Each column of data has a header and I am using the Auto-filter
gt; feature for
gt; gt; gt; gt; filtering. Is there a way that the COUNTIF function can be set up for
gt; each
gt; gt; gt; gt; ethnic code so that it will count from the filtered data? If not, is
gt; there
gt; gt; gt; gt; any other function, or nested functions, that will work?
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; RC
gt; gt; gt;
gt;
gt;
gt;