Another SUMPRODUCT SUBTOTAL question...

[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

 

[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

 

[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?

 

[Bob Phillips]

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

 

[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)

 

[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

 

[Trevor Williams]

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

 

[Mike H]

Glad I could help

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