Another SUMPRODUCT SUBTOTAL question...

T

Trevor Williams

Hi All

I've checked through the forum for the answer to my question, but with no
luck.
Can you tell me the correct syntax to use a SUBTOTAL Average in a SUMPRODUCT
Formula. (my data will be filtered hence the Subtotal)

I'm trying to get an AVERAGE of the values in range("H8:H20") if the
adjacent cell in range("G8:H20") = "Business Strategy" -- after the filter
has been applied.

Thanks in advance

Trevor Williams
 
M

Mike H

Trevor,

Why don't you apply a secondary filter to G8 - G20 for "Business Strategy"
then use a standard subtotal formula?

=SUBTOTAL(101,H8:H20)


Mike
 
T

Trevor Williams

Hi Mike -- the sheet is filtered by a user from another sheet - range G8:G20
could contain many things, and I actually need to do a subtotal for each of
the values that could appear in the range. I only chose one value for my
example to make it simple.

Any ideas?
 
B

Bob Phillips

=SUMPRODUCT(SUBTOTAL(1,OFFSET(H8,ROW($H$8:$H$20)-ROW($H$8),,1)),--($G$8:$G$120="Business
Strategy"))
 
T

Trevor Williams

Hi Bob - thanks for the response.

Something perculiar is happening - even though the SUBTOTAL(1) is being used
(i.e. Average) the formula is returning a sum. e.g. there are 2 occassions
where 'Business Strategy' is in the range - one with a 3 and one with a 4.
The formula returns 7, not 3.5...

hmmm, any ideas?

(oh, and I had to tweek the formula slightly to reduce the $G$120 to $G$20)
 
M

Mike H

Hi,

This works bit it's long winded

=SUMPRODUCT(SUBTOTAL(3,OFFSET(G7,ROW(G8:G20)-ROW($G$7),,1)),--($G$8:$G$20="Business
Strategy"),$H$8:$H$20)/SUMPRODUCT(SUBTOTAL(3,OFFSET(G7,ROW($G$8:$G$20)-ROW($G$7),,1)),--($G$8:$G$20="Business Strategy"))

Mike
 
T

Trevor Williams

Bingo! - If long-winded works, then long-winded it'll be!
Thanks Mike, much appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top