Home |
Search |
Today's Posts |
#1
|
|||
|
|||
counting filtered items
I have spreadsheet with about 20 data fields (columns). But for
simplicity I've created my question just using a few fields - but the answer will really apply to the additional fields I have on my spreadsheet. I have a spreadsheet the contains the year a new employee was hired, their name and their sex. A number of different people will be viewing this and I would like to allow them to filter what items they'd like to see and then creat a count on the M (males) and F (females) found on the filtered items. I was thinking @countif would work, but it does not apply just to the filtered data. Is there another function I'm not aware of? Thanks for the help |
#2
|
|||
|
|||
SUBTOTAL is the one...........
From Help: SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered. Vaya con Dios, Chuck, CABGx3 "mar10" wrote in message oups.com... I have spreadsheet with about 20 data fields (columns). But for simplicity I've created my question just using a few fields - but the answer will really apply to the additional fields I have on my spreadsheet. I have a spreadsheet the contains the year a new employee was hired, their name and their sex. A number of different people will be viewing this and I would like to allow them to filter what items they'd like to see and then creat a count on the M (males) and F (females) found on the filtered items. I was thinking @countif would work, but it does not apply just to the filtered data. Is there another function I'm not aware of? Thanks for the help |
#3
|
|||
|
|||
You might also consider using a pivot table, which will keep formulas out of
your raw data, yet provide the flexibility to generate a variety of reports. "CLR" wrote: SUBTOTAL is the one........... From Help: SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered. Vaya con Dios, Chuck, CABGx3 "mar10" wrote in message oups.com... I have spreadsheet with about 20 data fields (columns). But for simplicity I've created my question just using a few fields - but the answer will really apply to the additional fields I have on my spreadsheet. I have a spreadsheet the contains the year a new employee was hired, their name and their sex. A number of different people will be viewing this and I would like to allow them to filter what items they'd like to see and then creat a count on the M (males) and F (females) found on the filtered items. I was thinking @countif would work, but it does not apply just to the filtered data. Is there another function I'm not aware of? Thanks for the help |
#4
|
|||
|
|||
Let D3:D400 houses sex values, excluding the header...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D400,ROW(D3:D400)-ROW(D3),,1)),--(D3:D400="M")) would yield a count males from the area filtered on a set of criteria applied to other fields. mar10 wrote: I have spreadsheet with about 20 data fields (columns). But for simplicity I've created my question just using a few fields - but the answer will really apply to the additional fields I have on my spreadsheet. I have a spreadsheet the contains the year a new employee was hired, their name and their sex. A number of different people will be viewing this and I would like to allow them to filter what items they'd like to see and then creat a count on the M (males) and F (females) found on the filtered items. I was thinking @countif would work, but it does not apply just to the filtered data. Is there another function I'm not aware of? Thanks for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I count items in a filtered list? | Excel Worksheet Functions | |||
counting number of particular items in a list | Excel Discussion (Misc queries) | |||
Counting within a filtered range | Excel Worksheet Functions | |||
counting similar items in a column | Excel Discussion (Misc queries) | |||
Counting Unique Cells When Spread Sheet is Filtered | Excel Worksheet Functions |