sum based on the names in the list

[navin]

Hi,

I have a sheet, in which, col A contains name of suppliers (sorted
alphabatically) and col B contains qtys which was delivered by the
supplier.

Now i have get the sum of the qty based on the supplier.


Supplier Name Sum of Delivered Qty
AAAAA 56
AAAAA 30
AAAAA 17
AAAAA 8
AAAAA 8
AAAAA 1
AAAAA 10
BBBB 20
BBBB 40
BBBB 55
BBBB 65
BBBB 8
BBBB 1
BBBB 10

is there any formula, which will give me the sum, supplier wise. I know
this can be achieved by doing pivot, but i do not want to do a Pivot
because after the sum, total by supplier will be divided by each
deliverd qty.

Hope my problem is clear.

Thanks,
Navin

 

[Don Guillett]

Have a look in the help index for SUMIF

 

[Roger Govier]

Hi

Try
=SUMIF(A:A,"AAAA",B:B)

Better still, make a list of suppliers in say column E, then in F1 enter
=SUMIF(A:A,E1,B:B)
and copy down

 

[Earl Kiosterud]

Navin,

You said "after the sum, total by supplier will be divided by each deliverd
qty." I suspect you may have meant the other way around, to yield the
percentage of the supplier's total of each delivered quantity . Is that the
case?

If so, a SUMPRODUCT formula can be used in each row to get the supplier. In
C2 you could use:
=B2/SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10))
and copy down.