| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum Visible Cells | Excel Worksheet Functions | |||
| Sum of visible cells only | Excel Worksheet Functions | |||
| How do I calculate only visible data (not hidden rows)? | Excel Worksheet Functions | |||
| sum only visible cells | Excel Worksheet Functions | |||
| SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY | Excel Worksheet Functions | |||
Linear Mode
