SUMPRODUCT TO CALCULATE VISIBLE CELLS ONLY

[Lisa]

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]

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),--(D210))

would total the visible cells in column D.

 

[Lisa]

Debra, I'm sorry, I did not put the full formula that I am having a
problem with. It is:
SUMPRODUCT(B8:B250,D8250)/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.

 

[Aladin Akyurek]

You still need the Longre idiom Debra invokes...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B8:B250,ROW(B8:B250)-MIN(ROW(B8:B250)),,1)),
B8:B250,D8250)/SUBTOTAL(9,B8:B250)

 

[Lisa]

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.