Combining subtotal and sumif functions

[TPDigg]

I would like to combine the sumif function with filters and sum only the data
chosen in the filter. Subtotal (109,xxx) would ignore the hidden data but I
am having trouble combining the functions sumif & subtotal. Help much
appreciated.

 

[Domenic]

Try...

=SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(109,OFFSET(RangeToSum,ROW(Range
ToSum)-MIN(ROW(RangeToSum)),0,1)))

Note that if the condition is a numerical value, remove the quotes.

Hope this helps!

 

[TPDigg]

Domenic,

thanks for the attempt but that seems to be returning a 0 when there should
be data. I would have thought something like this would work but it doesn't
- =sumif(range,criteria,subtotal(109,range)). I am far from an expert, do
you see why this isn't working?

Thanks

 

[TPDigg]

Domenic,

Perfect!!! I got it to work. Thank you so much for the help. You saved me
days of work.