Arrays and Average
 

I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.

Arrays and Average
 

=SUMIF(B1:B25,"US",C1:C25)/COUNT(1/IF(B1:B25="US",MATCH(A1:A25,A1:A25,0)=ROW($A1:A25)-ROW(A1)+1))

it is an array formula, so commit with ctrl-shift-enter

--
__________________________________
HTH

Bob

"PAL" wrote in message
...
I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the
products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.

Arrays and Average
 

PAL wrote:
I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.


Two possible solutions he

http://www.savefile.com/files/2001590

Arrays and Average
 

try

=SUMPRODUCT(--(A2:A10="ABC"),--(B2:B10="us"),C2:C10)/SUMPRODUCT(--(A2:A10="ABC"),--(B2:B10="US"))
or
=SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2),C2:C10)/SUMPRODUCT(--(A2:A10=D2),--(B2:B10=E2))

D2 and E2 are where you type the product name and region

this give you the average of the sum based on product and region

HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis





"PAL" wrote:

I am working w/ a table. Lets say 25 rows/3 columns

Col 1 is product name
Col 2 is region name
Col 3 is number of units sold

I would like to get the average number of units for the sum of the products.
So if there are 2 products in the US that sold a combined 15 units, the
average would be 7.5.

I would like to do this for each region.

Please advise on formula. Thanks.