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


--
__________________________________
HTH

Bob

"Trevor Williams" wrote in
message ...
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

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)


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Trevor Williams" wrote in
message ...
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

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" wrote:

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)


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Trevor Williams" wrote in
message ...
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

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

"Mike H" wrote:

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" wrote:

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)


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Trevor Williams" wrote in
message ...
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

Glad I could help

"Trevor Williams" wrote:

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

"Mike H" wrote:

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" wrote:

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)


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Trevor Williams" wrote in
message ...
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