Sum If Visible
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! |
Sum If Visible
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! |
Sum If Visible
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! |
Sum If Visible
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 |
Sum If Visible
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! |
Sum If Visible
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! |
All times are GMT +1. The time now is 08:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com