G
guilbj2
I'm using a sheet with filtered results and have used subtotal to ensure
that some averages update dynamically based on filtered results. One of
the fields requires me to use =sumproduct to calculate the correct
numbers for an average. The following formula works without the
filters...
=SUMPRODUCT(G8:G34*H8:H34)/SUM(G8:G34)
I've adjusted it to read
=SUMPRODUCT(G8:G34*H8:H34)/SUBTOTAL(9,G8:G34)
The issue I'm faced with is getting the ranges (G8:G34 and H8:H34) to
change everytime I change the filter. The list of Function Numbers for
Subtotal does not include an option for sumproduct. Is there a way
around this ?
that some averages update dynamically based on filtered results. One of
the fields requires me to use =sumproduct to calculate the correct
numbers for an average. The following formula works without the
filters...
=SUMPRODUCT(G8:G34*H8:H34)/SUM(G8:G34)
I've adjusted it to read
=SUMPRODUCT(G8:G34*H8:H34)/SUBTOTAL(9,G8:G34)
The issue I'm faced with is getting the ranges (G8:G34 and H8:H34) to
change everytime I change the filter. The list of Function Numbers for
Subtotal does not include an option for sumproduct. Is there a way
around this ?