REPOST: Count certain records in filtered data

  • Thread starter Thread starter Steve Simons
  • Start date Start date
S

Steve Simons

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