counting filtered items

  • Thread starter Thread starter mar10
  • Start date Start date
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
 
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
 
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.
 
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.
 
Back
Top