Sum/Frequency Formula

[SiH23]

In Column B I have listed various product codes, these codes are a mixture of
both alpha and numerical characters, some are duplicated several times in the
column.

Each product code has an associated payment total in £ sterling in Column I.

I would like to place a formula in Column K which will show the associated
payment 'total' for each product code.

 

[Luke M]

Do you have a list somewhere of all the unique product codes? I'll assume its
in column J.
Placing this in K2:
=SUMIF(B:B,J2,I:I)
Will give you the total sum for the product code you have in J2.

 

[Bernard Liengme]

In K1 enter =SUMIF(B:B,B1,I:I) ( that is the letters I,
colon , letter I)
Copy down the column
Of course, you will get duplicates

If you had a list of unique product codes starting in K1, then in L1 use
=SUMIF(B:B,K1,I:I)
and copy down to the last K value

But Pivot Tables are ideal for this type of work.
Read a few of these and then come back with questions

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes

 

[Eduardo]

Hi,
lets say you have in columng J a list of your codes for you want the totals
(starting in J2, in column K enter
=SUMPRODUCT(--(B:B=J2),I:I)

 

[Luke M]

Note that you can only use entire columns with SUMPRODUCT in Excel 2007.

 

[SiH23]

There isn't a column with 'unique values' within the worksheet I'm afraid. I
have tried placing B2 within the formula instead of J2, but am getting very
odd results. Any help would be greatly appreciated.

 

[SiH23]

There isn't a column with 'unique values' within the worksheet I'm afraid. I
have tried placing B2 within the formula instead of J2, but am getting very
odd results. Any help would be greatly appreciated.

 

[SiH23]

Hi Bernard,

Many, many thanks the =SUMIF(B:B,B1,I:I) did the trick very nicely.

Kind regards,

Simon.