Calculate visible cells only?
 

Excel2003 ...

Row 1 ... Cols D-E-F-G-H-I-J ... contain SUMIF Formula with Range (D4:D100)
changed for each respective Column. Works great when Rows not Filtered.

Cell D1 contains ... =SUMIF(D4:D100,"0",K4:K100)
Cell E1 contains ... = SUMIF(E4:E100,"0",K4:K100)
etc

Row 2 (same Cols) contains SUBTOTAL Formulas (no issue).

Row 3 contains my Filter Switches

Issue ... Is there a way to write formula in Row 1 so it too will only
return the value of the Filtered rows as does SUBTOTAL?

Thanks ... Kha

Calculate visible cells only?
 

Enter this formula in D1 and copy across to J1:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4:D100,ROW(D4:D100)-ROW(D4),0,1)),--(D4:D1000),$K4:$K100)


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Excel2003 ...

Row 1 ... Cols D-E-F-G-H-I-J ... contain SUMIF Formula with Range
(D4:D100)
changed for each respective Column. Works great when Rows not Filtered.

Cell D1 contains ... =SUMIF(D4:D100,"0",K4:K100)
Cell E1 contains ... = SUMIF(E4:E100,"0",K4:K100)
etc

Row 2 (same Cols) contains SUBTOTAL Formulas (no issue).

Row 3 contains my Filter Switches

Issue ... Is there a way to write formula in Row 1 so it too will only
return the value of the Filtered rows as does SUBTOTAL?

Thanks ... Kha

Calculate visible cells only?
 

Works exactly as requested ... Thank you ... Kha

"T. Valko" wrote:

Enter this formula in D1 and copy across to J1:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4:D100,ROW(D4:D100)-ROW(D4),0,1)),--(D4:D1000),$K4:$K100)


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Excel2003 ...

Row 1 ... Cols D-E-F-G-H-I-J ... contain SUMIF Formula with Range
(D4:D100)
changed for each respective Column. Works great when Rows not Filtered.

Cell D1 contains ... =SUMIF(D4:D100,"0",K4:K100)
Cell E1 contains ... = SUMIF(E4:E100,"0",K4:K100)
etc

Row 2 (same Cols) contains SUBTOTAL Formulas (no issue).

Row 3 contains my Filter Switches

Issue ... Is there a way to write formula in Row 1 so it too will only
return the value of the Filtered rows as does SUBTOTAL?

Thanks ... Kha

Calculate visible cells only?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Works exactly as requested ... Thank you ... Kha

"T. Valko" wrote:

Enter this formula in D1 and copy across to J1:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(D4:D100,ROW(D4:D100)-ROW(D4),0,1)),--(D4:D1000),$K4:$K100)


--
Biff
Microsoft Excel MVP


"Ken" wrote in message
...
Excel2003 ...

Row 1 ... Cols D-E-F-G-H-I-J ... contain SUMIF Formula with Range
(D4:D100)
changed for each respective Column. Works great when Rows not
Filtered.

Cell D1 contains ... =SUMIF(D4:D100,"0",K4:K100)
Cell E1 contains ... = SUMIF(E4:E100,"0",K4:K100)
etc

Row 2 (same Cols) contains SUBTOTAL Formulas (no issue).

Row 3 contains my Filter Switches

Issue ... Is there a way to write formula in Row 1 so it too will only
return the value of the Filtered rows as does SUBTOTAL?

Thanks ... Kha