Combining subtotal and sumif functions
 

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.

Combining subtotal and sumif functions
 

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!

In article ,
TPDigg wrote:

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.

Combining subtotal and sumif functions
 

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

"Domenic" wrote:

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!

In article ,
TPDigg wrote:

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.

Combining subtotal and sumif functions
 

Domenic,

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

"Domenic" wrote:

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!

In article ,
TPDigg wrote:

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.