Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY
I have a formula at the top of a columnn to calculate the SUMPRODUCT.
However, I need the SUMPRODUCT to recalculate the visible cells only when I filter the column. I don't think that the SUBTOTAL function will work. Any suggestions? |
#2
|
|||
|
|||
There's an example here that counts visible cells in a filtered column.
You could modify the SUMPRODUCT formula to reference your cells, and add one more argument that refers to the cells to be totalled. http://www.contextures.com/xlFunctions04.html#Visible In the above example, the modified formula: =SUMPRODUCT(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-MIN(ROW(A2:A10)),,1)), --(A2:A10=A12),--(D2:D10)) would total the visible cells in column D. Lisa wrote: I have a formula at the top of a columnn to calculate the SUMPRODUCT. However, I need the SUMPRODUCT to recalculate the visible cells only when I filter the column. I don't think that the SUBTOTAL function will work. Any suggestions? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Debra, I'm sorry, I did not put the full formula that I am having a
problem with. It is: SUMPRODUCT(B8:B250,D8:D250)/SUM(B8:B250). I want this formula to recalculate and give me the result of the visible cells only when I filter. How would I modify the formula you gave above to work with this? thanks for your help. |
#4
|
|||
|
|||
You still need the Longre idiom Debra invokes...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8:B250,ROW(B8:B250)-MIN(ROW(B8:B250)),,1)), B8:B250,D8:D250)/SUBTOTAL(9,B8:B250) Lisa wrote: Debra, I'm sorry, I did not put the full formula that I am having a problem with. It is: SUMPRODUCT(B8:B250,D8:D250)/SUM(B8:B250). I want this formula to recalculate and give me the result of the visible cells only when I filter. How would I modify the formula you gave above to work with this? thanks for your help. |
#5
|
|||
|
|||
Thanks Aladin & Debra. I just started a new job that is heavy in Excel
and I am a relatively new user, so your reponses were greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct on filtered cells | Excel Worksheet Functions | |||
How do I calculate ratios between 2 cells? | Excel Discussion (Misc queries) | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions | |||
Strange Problem with Chart and Plot Visible Cells Option | Charts and Charting in Excel | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) |