Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records in filtered data
Hi
I have seen from looking through this NG how to use subtotal to count or sum filtered data, which is great, but I've been hunting around for nearly an hour to find the answer to what I need to do. I'm sure it can't be too difficult, but I'm having a one of dem days . . . I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. TIA Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records in filtered data
What exactly do you want to have as a result when you are done? The
approach may change depending upon your needs. "Steve Simons" wrote: Hi I have seen from looking through this NG how to use subtotal to count or sum filtered data, which is great, but I've been hunting around for nearly an hour to find the answer to what I need to do. I'm sure it can't be too difficult, but I'm having a one of dem days . . . I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. TIA Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records in filtered data
Subtotal should only count/sum visible data. What are you filtering/summing
on? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steve Simons" wrote in message ... Hi I have seen from looking through this NG how to use subtotal to count or sum filtered data, which is great, but I've been hunting around for nearly an hour to find the answer to what I need to do. I'm sure it can't be too difficult, but I'm having a one of dem days . . . I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. TIA Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records in filtered data
Thanks for the quick responses. I'm filtering on a field other than the
'department' one - for example, the gender field to see all females. So, following on from my previous example, in total in my dbs I have 3000 records and 20 with a department of Accounts. When I filter on gender of female I have maybe 200 records visible, but only 15 of them contain a department of "Accounts", (there are only 15 females working in the accounts dept), so I want a formula that will look at the filtered data and return how many VISIBLE records in the department column have a department of "Accounts" i.e. 15. Using subtotal on it's own returns a total of 20, as it counts visible and hidden records. I hope that's a bit clearer. Thanks again Steve I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. Bob Phillips wrote: Subtotal should only count/sum visible data. What are you filtering/summing on? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steve Simons" wrote in message ... Hi I have seen from looking through this NG how to use subtotal to count or sum filtered data, which is great, but I've been hunting around for nearly an hour to find the answer to what I need to do. I'm sure it can't be too difficult, but I'm having a one of dem days . . . I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. TIA Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count certain records in filtered data
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$3000)-ROW($C$1),,1)),--($C$2:$C
$3000="department")) as an example -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ps.com... Thanks for the quick responses. I'm filtering on a field other than the 'department' one - for example, the gender field to see all females. So, following on from my previous example, in total in my dbs I have 3000 records and 20 with a department of Accounts. When I filter on gender of female I have maybe 200 records visible, but only 15 of them contain a department of "Accounts", (there are only 15 females working in the accounts dept), so I want a formula that will look at the filtered data and return how many VISIBLE records in the department column have a department of "Accounts" i.e. 15. Using subtotal on it's own returns a total of 20, as it counts visible and hidden records. I hope that's a bit clearer. Thanks again Steve I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. Bob Phillips wrote: Subtotal should only count/sum visible data. What are you filtering/summing on? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Steve Simons" wrote in message ... Hi I have seen from looking through this NG how to use subtotal to count or sum filtered data, which is great, but I've been hunting around for nearly an hour to find the answer to what I need to do. I'm sure it can't be too difficult, but I'm having a one of dem days . . . I have filtered data, let's say in column A I have departments listed, with 20 occurences of dept "Accounts". When I filter the data on another field only 15 occurrences of "Accounts" are visible. I need to be able to count (and and also sum) the number of occurences of "Accounts" within the filtered data. If I use subtotal for this I get 20 returned. TIA Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import filtered data | Excel Discussion (Misc queries) | |||
Macro question | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
How can the count of filtered records always display? | Excel Worksheet Functions |