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? |
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 |
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. |
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. |
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. |
All times are GMT +1. The time now is 03:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com