Posted to microsoft.public.excel.misc
|
|
REPOST: Count certain records in filtered data
Some notes...
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Steve Simons wrote:
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
On Fri, 18 Aug 2006 11:19:35 -0500, Dave Peterson
wrote:
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.)
Steve Simons wrote:
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
On Fri, 18 Aug 2006 08:01:13 -0500, Dave Peterson
wrote:
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)
Steve Simons wrote:
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
--
Dave Peterson
|