counting filtered items

M

mar10

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
 
C

CLR

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
 
W

wccmgr

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.
 
A

Aladin Akyurek

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.
 
Top