Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered
I have a constantly updated master sheet of individual student's exam results
- in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr" nearly achieved "NA" not achieved. I then use "=master sheet:a1" etc to mirror all information to a number of new sheets in the same document where the data is autofiltered by course code, to show only results of each specified faculty. What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to get a total of the number of students who have sat Level 1. The problem is that when i do this, it is also counting the "hidden" entries that are not part of the filter. Could anyone please advise on a method of counting ONLY the results shown after i fun my auto-filter? Thanks a lot, Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered
Paul,
Don't use a formula - use a pivot table, and drop all your criteria fields onto either the row or column area, and drop exam level onto the data field. Then, instead of using filters, select the values that you want to see by checking/ unchecking them after clicking the dropdown arrow. IF you do use a formula, use SUMPRODUCT, with your criteria included, like =SUMPRODUCT((A1:A692="A")*(J1:J692="L1")) HTH, Bernie MS Excel MVP "pmdoherty" wrote in message ... I have a constantly updated master sheet of individual student's exam results - in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr" nearly achieved "NA" not achieved. I then use "=master sheet:a1" etc to mirror all information to a number of new sheets in the same document where the data is autofiltered by course code, to show only results of each specified faculty. What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to get a total of the number of students who have sat Level 1. The problem is that when i do this, it is also counting the "hidden" entries that are not part of the filter. Could anyone please advise on a method of counting ONLY the results shown after i fun my auto-filter? Thanks a lot, Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered
Try this
=SUMPRODUCT(SUBTOTAL(3,OFFSET(J1:J692,ROW(J1:J692)-MIN(ROW(J1:J692)),,1))*(J1:J692="L1")) Mike "pmdoherty" wrote: I have a constantly updated master sheet of individual student's exam results - in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr" nearly achieved "NA" not achieved. I then use "=master sheet:a1" etc to mirror all information to a number of new sheets in the same document where the data is autofiltered by course code, to show only results of each specified faculty. What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to get a total of the number of students who have sat Level 1. The problem is that when i do this, it is also counting the "hidden" entries that are not part of the filter. Could anyone please advise on a method of counting ONLY the results shown after i fun my auto-filter? Thanks a lot, Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered
Hi,
If you have filtered on L1 and want to count the total number of visible cells of the filtered range, then you can use the SUBTOTAL() function =subtotal(3,J1:J692) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "pmdoherty" wrote in message ... I have a constantly updated master sheet of individual student's exam results - in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr" nearly achieved "NA" not achieved. I then use "=master sheet:a1" etc to mirror all information to a number of new sheets in the same document where the data is autofiltered by course code, to show only results of each specified faculty. What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to get a total of the number of students who have sat Level 1. The problem is that when i do this, it is also counting the "hidden" entries that are not part of the filter. Could anyone please advise on a method of counting ONLY the results shown after i fun my auto-filter? Thanks a lot, Paul |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficulty "countif"-ing number of "L1" or "L2" on an autofilt
Thanks Bernie - that's relly helpful and next time I will try it, but for now
Mike's formula has worked a treat. Thanks a lot - you've saved the day! Thanks to everyone who has offered help here. Regards, Paul "Bernie Deitrick" wrote: Paul, Don't use a formula - use a pivot table, and drop all your criteria fields onto either the row or column area, and drop exam level onto the data field. Then, instead of using filters, select the values that you want to see by checking/ unchecking them after clicking the dropdown arrow. IF you do use a formula, use SUMPRODUCT, with your criteria included, like =SUMPRODUCT((A1:A692="A")*(J1:J692="L1")) HTH, Bernie MS Excel MVP "pmdoherty" wrote in message ... I have a constantly updated master sheet of individual student's exam results - in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr" nearly achieved "NA" not achieved. I then use "=master sheet:a1" etc to mirror all information to a number of new sheets in the same document where the data is autofiltered by course code, to show only results of each specified faculty. What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to get a total of the number of students who have sat Level 1. The problem is that when i do this, it is also counting the "hidden" entries that are not part of the filter. Could anyone please advise on a method of counting ONLY the results shown after i fun my auto-filter? Thanks a lot, Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |