Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 "RC" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 "Elkar" wrote: 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 "RC" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 "RC" wrote: 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 "Elkar" wrote: 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 "RC" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 , RC wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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 "Elkar" wrote: 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 "RC" wrote: 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 "Elkar" wrote: 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 "RC" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
Thanks Domenic, this works fine.
RC "Domenic" wrote: 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 , RC wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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. "Domenic" wrote: 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 , RC wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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) "andy62" wrote in message ... 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. "Domenic" wrote: 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 , RC wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
So it's okay that the references inside the Subtotal function only address
the one column (in this case, A)? Awesome, thanks. "Bob Phillips" wrote: 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) "andy62" wrote in message ... 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. "Domenic" wrote: 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 , RC wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Will the COUNTIF function work with auto-filtering?
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) "andy62" wrote in message ... So it's okay that the references inside the Subtotal function only address the one column (in this case, A)? Awesome, thanks. "Bob Phillips" wrote: 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) "andy62" wrote in message ... 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. "Domenic" wrote: 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 , RC wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Extend Formulas doesn't work for a particular formula | Excel Discussion (Misc queries) | |||
COUNTIF doesnt work! | Excel Worksheet Functions | |||
How can I use the NOW function and keep it from auto updating? | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Countif Function -Nested | Excel Discussion (Misc queries) |