Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Hi
I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Are you actually filtering to hide the rows with male in that column?
Or are you just hiding those rows? I've never seen =subtotal() make a mistake in counting visible data. I put my =subtotal() formulas in Row 1 my headers in row 2 and my data in Rows 3:xxxx (No other stuff below my data) And my subtotal formulas look like: =subtotal(3,a3:a65536) to count the number of visible (Non-empty) entries in that column. ==== If you're using xl2003 and have hidden the rows manually, you can use: =subtotal(103,a3:a65536) Steve Simons wrote: Hi I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Hi Dave
Thanks for the advice. I've obviously not explained this clearly enough. The subtotal you descibe is working fine, but isn't what I want. I want the number of visible (non-empty) cells that contain the word "Accounts" In the example below =subtotal(3,a3:a65536) would return 10 DEPARTMENT GENDER Accounts F Accounts F Accounts M Accounts M Administration M Administration F Computer M Credit Control M Maintenance F Management M If I then filter the data on the Gender column, for F only, I want the subtotal to return 2 - the number of visible entries in column A that contain the word Accounts. DEPARTMENT GENDER Accounts F Accounts F Administration F Maintenance F Thanks again Steve On Fri, 18 Aug 2006 08:01:13 -0500, Dave Peterson wrote: Are you actually filtering to hide the rows with male in that column? Or are you just hiding those rows? I've never seen =subtotal() make a mistake in counting visible data. I put my =subtotal() formulas in Row 1 my headers in row 2 and my data in Rows 3:xxxx (No other stuff below my data) And my subtotal formulas look like: =subtotal(3,a3:a65536) to count the number of visible (Non-empty) entries in that column. ==== If you're using xl2003 and have hidden the rows manually, you can use: =subtotal(103,a3:a65536) Steve Simons wrote: Hi I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Why not just filter to show Accounts in that Department column.
But if you want... Saved from a previous post: Aladin Akyurek posted this: If you're trying to count the occurrences of a certain text in V which is part of an AutoFiltered range.... =SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange)-MIN(ROW(Vrange)),,1)), --(Vrange="Rome")) would calculate the frequency of occurrence of "Rome" in Vrange, the range in column V in the area subjected to AutoFilter. === that formula sits in one cell. And if you wanted to count the number of Rome's that appear in B2:B99 after you filter on some other column (mixture of Rome, Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace Vrange with B2:B99 in that formula. === So your formula may look more like: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A99,ROW(A2:A99)-MIN(ROW(A2:A99)),,1)), --(A2:A99="Accounts")) (Yep. I keep Aladin's formula handy in case I need it.) Steve Simons wrote: Hi Dave Thanks for the advice. I've obviously not explained this clearly enough. The subtotal you descibe is working fine, but isn't what I want. I want the number of visible (non-empty) cells that contain the word "Accounts" In the example below =subtotal(3,a3:a65536) would return 10 DEPARTMENT GENDER Accounts F Accounts F Accounts M Accounts M Administration M Administration F Computer M Credit Control M Maintenance F Management M If I then filter the data on the Gender column, for F only, I want the subtotal to return 2 - the number of visible entries in column A that contain the word Accounts. DEPARTMENT GENDER Accounts F Accounts F Administration F Maintenance F Thanks again Steve On Fri, 18 Aug 2006 08:01:13 -0500, Dave Peterson wrote: Are you actually filtering to hide the rows with male in that column? Or are you just hiding those rows? I've never seen =subtotal() make a mistake in counting visible data. I put my =subtotal() formulas in Row 1 my headers in row 2 and my data in Rows 3:xxxx (No other stuff below my data) And my subtotal formulas look like: =subtotal(3,a3:a65536) to count the number of visible (Non-empty) entries in that column. ==== If you're using xl2003 and have hidden the rows manually, you can use: =subtotal(103,a3:a65536) Steve Simons wrote: Hi I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Hi Dave
Fantastic! Thanks a lot. I was thrown for a while by the double minuses, as they happened to fall where there was a line break, and I thought they were a continuation sign, rather than part of the formula. I can see why you keep Aladins formula handy. I shall never leave home without it ;o) Steve On Fri, 18 Aug 2006 11:19:35 -0500, Dave Peterson wrote: Why not just filter to show Accounts in that Department column. But if you want... Saved from a previous post: Aladin Akyurek posted this: If you're trying to count the occurrences of a certain text in V which is part of an AutoFiltered range.... =SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange )-MIN(ROW(Vrange)),,1)), --(Vrange="Rome")) would calculate the frequency of occurrence of "Rome" in Vrange, the range in column V in the area subjected to AutoFilter. === that formula sits in one cell. And if you wanted to count the number of Rome's that appear in B2:B99 after you filter on some other column (mixture of Rome, Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace Vrange with B2:B99 in that formula. === So your formula may look more like: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A99,ROW(A2:A99 )-MIN(ROW(A2:A99)),,1)), --(A2:A99="Accounts")) (Yep. I keep Aladin's formula handy in case I need it.) Steve Simons wrote: Hi Dave Thanks for the advice. I've obviously not explained this clearly enough. The subtotal you descibe is working fine, but isn't what I want. I want the number of visible (non-empty) cells that contain the word "Accounts" In the example below =subtotal(3,a3:a65536) would return 10 DEPARTMENT GENDER Accounts F Accounts F Accounts M Accounts M Administration M Administration F Computer M Credit Control M Maintenance F Management M If I then filter the data on the Gender column, for F only, I want the subtotal to return 2 - the number of visible entries in column A that contain the word Accounts. DEPARTMENT GENDER Accounts F Accounts F Administration F Maintenance F Thanks again Steve On Fri, 18 Aug 2006 08:01:13 -0500, Dave Peterson wrote: Are you actually filtering to hide the rows with male in that column? Or are you just hiding those rows? I've never seen =subtotal() make a mistake in counting visible data. I put my =subtotal() formulas in Row 1 my headers in row 2 and my data in Rows 3:xxxx (No other stuff below my data) And my subtotal formulas look like: =subtotal(3,a3:a65536) to count the number of visible (Non-empty) entries in that column. ==== If you're using xl2003 and have hidden the rows manually, you can use: =subtotal(103,a3:a65536) Steve Simons wrote: Hi I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
REPOST: Count certain records in filtered data
Some notes...
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Steve Simons wrote: Hi Dave Fantastic! Thanks a lot. I was thrown for a while by the double minuses, as they happened to fall where there was a line break, and I thought they were a continuation sign, rather than part of the formula. I can see why you keep Aladins formula handy. I shall never leave home without it ;o) Steve On Fri, 18 Aug 2006 11:19:35 -0500, Dave Peterson wrote: Why not just filter to show Accounts in that Department column. But if you want... Saved from a previous post: Aladin Akyurek posted this: If you're trying to count the occurrences of a certain text in V which is part of an AutoFiltered range.... =SUMPRODUCT(SUBTOTAL(3,OFFSET(Vrange,ROW(Vrange )-MIN(ROW(Vrange)),,1)), --(Vrange="Rome")) would calculate the frequency of occurrence of "Rome" in Vrange, the range in column V in the area subjected to AutoFilter. === that formula sits in one cell. And if you wanted to count the number of Rome's that appear in B2:B99 after you filter on some other column (mixture of Rome, Paris, London still appear in B2:B99, you'd used Aladin's formula--just replace Vrange with B2:B99 in that formula. === So your formula may look more like: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A99,ROW(A2:A99 )-MIN(ROW(A2:A99)),,1)), --(A2:A99="Accounts")) (Yep. I keep Aladin's formula handy in case I need it.) Steve Simons wrote: Hi Dave Thanks for the advice. I've obviously not explained this clearly enough. The subtotal you descibe is working fine, but isn't what I want. I want the number of visible (non-empty) cells that contain the word "Accounts" In the example below =subtotal(3,a3:a65536) would return 10 DEPARTMENT GENDER Accounts F Accounts F Accounts M Accounts M Administration M Administration F Computer M Credit Control M Maintenance F Management M If I then filter the data on the Gender column, for F only, I want the subtotal to return 2 - the number of visible entries in column A that contain the word Accounts. DEPARTMENT GENDER Accounts F Accounts F Administration F Maintenance F Thanks again Steve On Fri, 18 Aug 2006 08:01:13 -0500, Dave Peterson wrote: Are you actually filtering to hide the rows with male in that column? Or are you just hiding those rows? I've never seen =subtotal() make a mistake in counting visible data. I put my =subtotal() formulas in Row 1 my headers in row 2 and my data in Rows 3:xxxx (No other stuff below my data) And my subtotal formulas look like: =subtotal(3,a3:a65536) to count the number of visible (Non-empty) entries in that column. ==== If you're using xl2003 and have hidden the rows manually, you can use: =subtotal(103,a3:a65536) Steve Simons wrote: Hi I have seen how to use subtotal to count or sum filtered data, which is great, but I can't find the answer to what I need to do:- I have filtered data. Let's say in column A I have departments listed, with 20 occurences of the department "Accounts". When I filter the data on another field (say column B, the "Gender" column, looking for only Females who work at the company), only 15 occurrences of "Accounts" are visible. I need to be able to count only the VISIBLE occurences of "Accounts" (i.e. 15 of them) within the filtered data. If I use subtotal for this I get 20 returned, when the answer should be 15. How can I get it to show me the 15 visible records "Accounts" for this data, filtered on Gender of "Female" ?? TIA Steve -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I COUNT CELLS WITH DATA BY COLUMN? | Excel Worksheet Functions | |||
count specific value with filtered data | Excel Worksheet Functions | |||
ranking query | Excel Discussion (Misc queries) | |||
Copying filtered data to another Excel Spreadsheet | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions |