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:OS T_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!

=SUMPRODUCT(--(OST_Raw_Data!A2:A1000=A2),--(OST_Raw_Data!C2:C1000="Tier 1"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cherman" wrote:

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:OS T_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!

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!


"Luke M" wrote:

=SUMPRODUCT(--(OST_Raw_Data!A2:A1000=A2),--(OST_Raw_Data!C2:C1000="Tier 1"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cherman" wrote:

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:OS T_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!

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?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cherman" wrote:

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!


"Luke M" wrote:

=SUMPRODUCT(--(OST_Raw_Data!A2:A1000=A2),--(OST_Raw_Data!C2:C1000="Tier 1"))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"cherman" wrote:

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:OS T_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!