|
Another SUMPRODUCT SUBTOTAL question...
=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 |
Another SUMPRODUCT SUBTOTAL question...
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 |
Another SUMPRODUCT SUBTOTAL question...
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 |
Another SUMPRODUCT SUBTOTAL question...
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 |
Another SUMPRODUCT SUBTOTAL question...
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 |
| All times are GMT +1. The time now is 06:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com