Summing Multiple References

[trafficbroker]

Hi all.

Im a bit of a beginner at Excel and have an annoyong problem that is driving me mad! I have long lists of references with values in adjacent cells. How do I total up the values and produce a list of non repeated references. Better explained like this:

Here is my origional list:

REF Value
7 2.096
7 2.351
7 1.741
19 1.645
7 11.843
7 4.227
4 3.266
11 3.376
4 1.972
11 1.729

I need to quickly sort the references into totals, this table would look like this:

REF Value
4 5.23
7 22.25
11 5.1
19 1.64

I can easily mannually do it by sorting A-Z then summing the values for each reference. But with 1000's of refs it takes too long.

I would greatly appreciate any help with this simple yet very annoying problem!

Thanks in advance,

Matt
TrafficBroker

[dominicb]

Good afternoon Trafficbroker

You have two options here :

1. Using SUMIF for each of your ref values eg
=SUMIF(B5:B14,4,C5:C14).

2. A simple pivot table would be easy to create and maintain, but the links in a pivot table are not as dynamic as an excel formula would be. You would have to update it manually.

HTH

DominicB

[Daminc]

Ah, I was beaten to it:

=SUMIF(A2:A11,4,B2:B11)

[trafficbroker]

Afternoon.

Wow guys, now that is a good service! You have saved me so much time. Great forum, I will be here more often!

Matt
TrafficBroker

[Suggy]

Hello all - this is my first post. I have a similar problem, but...
what if one does not know the total number of references?
i.e.
A B
7 2.096
7 2.351
7 1.741
19 1.645
7 11.843
7 4.227
4 3.266
11 3.376
4 1.972
11 1.729

but I do not know how many unique column A refereces there are - there could be 10 unique numbers or 3, but I want a total of the value for each unique number within a range.
thanks a lot!
Scott

[Suggy]

Hi again. This formual below seems to work, then I just have to sort the rows to condense the sheet.
=IF(B2<>B3,COUNTIF($B$2:$B$30,B2),"")
thanks