Is it possible to do a conditional subtotal in Excel?

[LTS_Bgobien]

Is it possible to do a conditional subtotal? I have used the Conditional Sum
feature many times, but I want to do a Conditional Subtotal, and I can't seem
to get it to work. Any Ideas?

 

[Frank Kabel]

Hi
do you mean a conditional sum based on a filtered list?

 

[LTS_Bgobien]

Yes, I would like to do a conditional sum based on a filtered list, but I
would like the sum value to represent the values shown by the filter only. As
far as I know only the subtotal function can do this, but I'm sure there must
be another way. Any help is appreciated.

 

[Frank Kabel]

Hi
try something like the following (summs all values in column C if
column B conatins 'value'):
=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),--($B$1:$B$10="value"),$C$1:$C$10)

 

[Frank Kabel]

Hi
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$200,1,1),ROW($A$1:$A$200
)-
ROW(INDEX($A$1:$A$200,1,1)),0))=1),--($G$1:$G$200>=DATE(2004,1,1)),$F$1
:$F$200)

If this does not work you may post your current sUMIF formula

 

[LTS_Bgobien]

That seemed to do the Trick...thanks for all your help Frank. If you wouldn't
mind, could you briefly explain how you got the steps in the formula. my
direct e-mail is (e-mail address removed)

Thanks again