REPOST: Count certain records in filtered data

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)


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

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
<[email protected]> 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

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
> <[email protected]> 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

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
<[email protected]> 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
>> <[email protected]> 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

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 here:
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
> <[email protected]> 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
> >> <[email protected]> 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