Another SUMPRODUCT SUBTOTAL question...

  • Thread starter Thread starter Trevor Williams
  • Start date Start date
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
 
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
 
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?
 
=SUMPRODUCT(SUBTOTAL(1,OFFSET(H8,ROW($H$8:$H$20)-ROW($H$8),,1)),--($G$8:$G$120="Business
Strategy"))
 
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)
 
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
 
Bingo! - If long-winded works, then long-winded it'll be!
Thanks Mike, much appreciated.
 
Back
Top