Remember Me?

#1
December 9th 04, 03:11 PM
 Sheryl Posts: n/a
Subtotal And Countif

First of all thanks to the three gentlemen who have pointed me in the right
direction using the Subtotal function in a previous post. I am half way
there! Using subtotal works for what I need to accomplish. What I am
trying to do is filter a list of entries and make calculations. I am
currently using countif if a cell contains a "Y" as a value for the
calculation. I need to filter a list with multiple same name entires and am
having trouble filtering the list because I cannot use the subtotal and
countif together. Ideally I would like to filter a column for a name that
has mulitply entries and have Excel calulate all of the cells that are tied
to that name for which contain the letter "Y". I thought I was on the right
track with the CountA(A:1,A5,"Y") function and then using
Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works
fine by itself but not with the subtotal function. I cannot use the
CountA(A:1,A5,"Y") function for my calculations due to these same filtered
fields having to contain other characters such as "N" and "NA". CountA will
count every cell that is not blank. Thanks in advance for any help that
anyone may be able to provide.

#2
December 9th 04, 04:41 PM
 Frank Kabel Posts: n/a

Hi
try:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(\$A\$1:\$A\$10,1,1),ROW(\$A\$1: \$A\$10)-
ROW(INDEX(\$A\$1:\$A\$10,1,1)),0))=1),--(\$A\$1:\$A\$10="Y"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Sheryl" schrieb im Newsbeitrag
...
First of all thanks to the three gentlemen who have pointed me in the

right
direction using the Subtotal function in a previous post. I am half

way
there! Using subtotal works for what I need to accomplish. What I

am
trying to do is filter a list of entries and make calculations. I am
currently using countif if a cell contains a "Y" as a value for the
calculation. I need to filter a list with multiple same name entires

and am
having trouble filtering the list because I cannot use the subtotal

and
countif together. Ideally I would like to filter a column for a name

that
has mulitply entries and have Excel calulate all of the cells that

are tied
to that name for which contain the letter "Y". I thought I was on

the right
track with the CountA(A:1,A5,"Y") function and then using
Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y")

works
fine by itself but not with the subtotal function. I cannot use the
CountA(A:1,A5,"Y") function for my calculations due to these same

filtered
fields having to contain other characters such as "N" and "NA".

CountA will
count every cell that is not blank. Thanks in advance for any help

that
anyone may be able to provide.

#3
December 9th 04, 04:45 PM
 Peo Sjoblom Posts: n/a

Why can't you first filter on the names and then filter on the Y, then just
count the list? You might also be able to add a column with formulas and
filter on first the other 2 then on the formulas. Finally there is a complex
solution that's using subtotal and sumproduct to do countif on a filtered
list but given the complexity maybe you could post back with more information
first to see if we could use other alternatives

Here's an example of a formulka that will count Y in column B of a filtered
list

=SUMPRODUCT(--(\$B\$2:\$B\$20="Y"),(SUBTOTAL(3,OFFSET(\$B\$2,ROW(\$B\$2: \$B\$20)-MIN(ROW(\$B\$2:\$B\$20)),,))))

Regards,

Peo Sjoblom

"Sheryl" wrote:

First of all thanks to the three gentlemen who have pointed me in the right
direction using the Subtotal function in a previous post. I am half way
there! Using subtotal works for what I need to accomplish. What I am
trying to do is filter a list of entries and make calculations. I am
currently using countif if a cell contains a "Y" as a value for the
calculation. I need to filter a list with multiple same name entires and am
having trouble filtering the list because I cannot use the subtotal and
countif together. Ideally I would like to filter a column for a name that
has mulitply entries and have Excel calulate all of the cells that are tied
to that name for which contain the letter "Y". I thought I was on the right
track with the CountA(A:1,A5,"Y") function and then using
Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works
fine by itself but not with the subtotal function. I cannot use the
CountA(A:1,A5,"Y") function for my calculations due to these same filtered
fields having to contain other characters such as "N" and "NA". CountA will
count every cell that is not blank. Thanks in advance for any help that
anyone may be able to provide.

#4
December 9th 04, 07:16 PM
 Sheryl Posts: n/a

Thanks Frank! That worked perfectly! - Sheryl

"Frank Kabel" wrote:

Hi
try:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX(\$A\$1:\$A\$10,1,1),ROW(\$A\$1: \$A\$10)-
ROW(INDEX(\$A\$1:\$A\$10,1,1)),0))=1),--(\$A\$1:\$A\$10="Y"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Sheryl" schrieb im Newsbeitrag
...
First of all thanks to the three gentlemen who have pointed me in the

right
direction using the Subtotal function in a previous post. I am half

way
there! Using subtotal works for what I need to accomplish. What I

am
trying to do is filter a list of entries and make calculations. I am
currently using countif if a cell contains a "Y" as a value for the
calculation. I need to filter a list with multiple same name entires

and am
having trouble filtering the list because I cannot use the subtotal

and
countif together. Ideally I would like to filter a column for a name

that
has mulitply entries and have Excel calulate all of the cells that

are tied
to that name for which contain the letter "Y". I thought I was on

the right
track with the CountA(A:1,A5,"Y") function and then using
Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y")

works
fine by itself but not with the subtotal function. I cannot use the
CountA(A:1,A5,"Y") function for my calculations due to these same

filtered
fields having to contain other characters such as "N" and "NA".

CountA will
count every cell that is not blank. Thanks in advance for any help

that
anyone may be able to provide.

#5
December 9th 04, 07:18 PM
 Sheryl Posts: n/a

Thanks Peo, will try this as well! - Sheryl

"Peo Sjoblom" wrote:

Why can't you first filter on the names and then filter on the Y, then just
count the list? You might also be able to add a column with formulas and
filter on first the other 2 then on the formulas. Finally there is a complex
solution that's using subtotal and sumproduct to do countif on a filtered
list but given the complexity maybe you could post back with more information
first to see if we could use other alternatives

Here's an example of a formulka that will count Y in column B of a filtered
list

=SUMPRODUCT(--(\$B\$2:\$B\$20="Y"),(SUBTOTAL(3,OFFSET(\$B\$2,ROW(\$B\$2: \$B\$20)-MIN(ROW(\$B\$2:\$B\$20)),,))))

Regards,

Peo Sjoblom

"Sheryl" wrote:

First of all thanks to the three gentlemen who have pointed me in the right
direction using the Subtotal function in a previous post. I am half way
there! Using subtotal works for what I need to accomplish. What I am
trying to do is filter a list of entries and make calculations. I am
currently using countif if a cell contains a "Y" as a value for the
calculation. I need to filter a list with multiple same name entires and am
having trouble filtering the list because I cannot use the subtotal and
countif together. Ideally I would like to filter a column for a name that
has mulitply entries and have Excel calulate all of the cells that are tied
to that name for which contain the letter "Y". I thought I was on the right
track with the CountA(A:1,A5,"Y") function and then using
Subtotal(3,A:1,A:5,"Y") but that will not work. CountA(A:1,A5,"Y") works
fine by itself but not with the subtotal function. I cannot use the
CountA(A:1,A5,"Y") function for my calculations due to these same filtered
fields having to contain other characters such as "N" and "NA". CountA will
count every cell that is not blank. Thanks in advance for any help that
anyone may be able to provide.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

All times are GMT +1. The time now is 01:01 PM.