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!

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....T.html#visible

if this helps please click yes, thanks

"lightbulb" wrote:

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!

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

"lightbulb" wrote:

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!

In article ,
lightbulb wrote:

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)))

--
Domenic
http://www.xl-central.com

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...

"Eduardo" wrote:

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....T.html#visible

if this helps please click yes, thanks

"lightbulb" wrote:

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!

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...

"Sheeloo" wrote:

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

"lightbulb" wrote:

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!