Help with formula

C

cherman

I have data and a formula like below. I'm trying to adjust the formula to
count the # of rows that have a value in Column A that is = to cell A2 on my
OST_Raw_Data sheet and where the value in Column C on my OST_Raw_Data sheet =
"Tier 1". The formula is not on the OST_Raw_Data sheet and "A2" is on the
same sheet as the formula.

I believe the part of the formula that references A2 is correct, but I'm
having problems with the rest. I'm using Excel 2003 and I need to keep the
Subtotal to allow for consideration of filtered rows. Can someone help?


COL A COL B COL C

WW_Yr Issue Number Tier
2009 WW10 538702 Tier 2
2009 WW11 540391 Tier 1


=SUMPRODUCT(SUBTOTAL(9,OFFSET(OST_Raw_Data!$C$2:OST_Raw_Data!$C$10000,ROW(OST_Raw_Data!$C$2:OST_Raw_Data!$C$10000)-ROW(OST_Raw_Data!$C$2),0,1)),--(OST_Raw_Data!$A$2:OST_Raw_Data!$A$10000=A2),--(OST_Raw_Data!$E$2:OST_Raw_Data!$E$10000=1))


Thanks!
 
C

cherman

Thanks for the try, but as I mentioned below, I need to keep the subtotal in
there because I need to factor out filtered rows.

Any suggestions?

Thanks!
 
L

Luke M

Sorry, I missed that in your first post. I'm afraid SUMPRODUCT won't work
with SUBTOTAL like that. Perhaps you could include your filtering criteria
into the formula?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top