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.
|