Count certain records in filtered data

[Steve Simons]

Hi

I have seen from looking through this NG how to use subtotal to count
or sum filtered data, which is great, but I've been hunting around for
nearly an hour to find the answer to what I need to do. I'm sure it
can't be too difficult, but I'm having a one of dem days . . .

I have filtered data, let's say in column A I have departments listed,
with 20 occurences of dept "Accounts". When I filter the data on
another field only 15 occurrences of "Accounts" are visible.

I need to be able to count (and and also sum) the number of occurences
of "Accounts" within the filtered data. If I use subtotal for this I
get 20 returned.


TIA

Steve

 

[Guest]

What exactly do you want to have as a result when you are done? The
approach may change depending upon your needs.

 

[Bob Phillips]

Subtotal should only count/sum visible data. What are you filtering/summing
on?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[simons_pctamers]

Thanks for the quick responses. I'm filtering on a field other than the
'department' one - for example, the gender field to see all females.
So, following on from my previous example, in total in my dbs I have
3000 records and 20 with a department of Accounts. When I filter on
gender of female I have maybe 200 records visible, but only 15 of them
contain a department of "Accounts", (there are only 15 females working
in the accounts dept), so I want a formula that will look at the
filtered data and return how many VISIBLE records in the department
column have a department of "Accounts" i.e. 15. Using subtotal on it's
own returns a total of 20, as it counts visible and hidden records.

I hope that's a bit clearer.

Thanks again

Steve

 

[Bob Phillips]

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$3000)-ROW($C$1),,1)),--($C$2:$C
$3000="department"))

as an example


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)