Sum If Visible

[lightbulb]

I have a formula in a cell to sum based on different critieria....
=SUMPRODUCT(--($B$7:$B$399="F"),--(AD7:AD399<200),AD7:AD399) now I've added
a filter and only want it to sum the cells that are visible (b/c the filter
only makes visible the certain cells)...is there a way to do this?

Thanks!

 

[Eduardo]

Hi,
Take a look to this webpage your case is in Example 11, but it has lots of
examples
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#visible

if this helps please click yes, thanks

 

[Sheeloo]

Use Subtotal function... It sums up filtered cells only...
Look in help for details.

 

[Domenic]

I have a formula in a cell to sum based on different critieria....
=SUMPRODUCT(--($B$7:$B$399="F"),--(AD7:AD399<200),AD7:AD399) now I've added
a filter and only want it to sum the cells that are visible (b/c the filter
only makes visible the certain cells)...is there a way to do this?

Thanks!

Try...

=SUMPRODUCT(--($B$7:$B$399="F"),--(AD7:AD399<200),SUBTOTAL(9,OFFSET(AD7:A
D399,ROW(AD7:AD399)-ROW(AD7),0,1)))

 

[lightbulb]

I can't get my Subtotal and SumProduct functions to work together...I have
the sumproduct formula working fine, but I can't figure out how to add the
subtotal in there so it disregards hidden cells...

 

[lightbulb]

I can't get my Subtotal and SumProduct functions to work together...I have
the sumproduct formula working fine, but I can't figure out how to add the
subtotal in there so it disregards hidden cells...