Subtotal And Countif

[Guest]

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.

 

[Frank Kabel]

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"))

 

[Guest]

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

 

[Guest]

Thanks Frank! That worked perfectly! - Sheryl

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"))

 

[Guest]

Thanks Peo, will try this as well! - Sheryl

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