Subtotal - Can I use Sumproduct ?
 

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 ?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=375088

Try something like

=SUMPRODUCT(--($G$8:$G$34),--($H$8:$H$34),--(SUBTOTAL(3,OFFSET($G$8,ROW($G$8:$G$34)-MIN(ROW($G$8:$G$34)),,))))/SUBTOTAL(9,$G$8:$G$34)

should only apply to visible cells

--
Regards,

Peo Sjoblom


"guilbj2" wrote in
message ...

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 ?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=375088

you can use sumproduct to "filter" out the data you want to average

=sumproduct((g8:g34=XXX)*(h8:h34))/sumproduct((g8:g34=XXX)*1)

for example


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=375088

I'm sorry, but I don't follow. What does XXX represent ?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile: http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=375088

It means whatever your criteria is however it won't work, the formula I
provided will only perform the calculations on visible cells

--
Regards,

Peo Sjoblom


"guilbj2" wrote in
message ...

I'm sorry, but I don't follow. What does XXX represent ?


--
guilbj2
------------------------------------------------------------------------
guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
View this thread: http://www.excelforum.com/showthread...hreadid=375088