Sum If Visible

  • Thread starter Thread starter lightbulb
  • Start date Start date
L

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!
 
lightbulb said:
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!
Click to expand...


Try...

=SUMPRODUCT(--($B$7:$B$399="F"),--(AD7:AD399<200),SUBTOTAL(9,OFFSET(AD7:A
D399,ROW(AD7:AD399)-ROW(AD7),0,1)))
 
lightbulb said:
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!
Click to expand...


Try...

=SUMPRODUCT(--($B$7:$B$399="F"),--(AD7:AD399<200),SUBTOTAL(9,OFFSET(AD7:A
D399,ROW(AD7:AD399)-ROW(AD7),0,1)))
 
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...
 
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...
 
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...
 
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...
 
Back
Top