Calculating total values of duplicate entries

[jonnybump]

Hello all,
This is going to be quite difficult to explain so I'll try my best and
hopefully I'll get it sorted out

OK, I have a spreadsheet with four columns in total. First column (A)
contains four-letter combinations (eg. AAAA, AABB, ABAA etc). Second
column (B) contains a value for each of the letter combinations in
column one (eg. 110, 1000, 7000 etc). The third column (C) I have
entered the code *=LEFT(A1,2)* to only show the first two letters of
the text in column one. This applies to the whole column, not just row
2 as in my example. This would give, for example, results of AA, AB, AC
and so on:

[image: http://www.adrenaline-shots.co.uk/sheet/spreadsheet1.gif]

Column four (D) is where my problem lies - I need to work out the total
value (SUM of the B column) of AA instances, AB instances etc. but I
would like it to be a simple formula that I can copy throughout the
whole column. The result I need to achieve is shown in the following
image, but as I said it's time-consuming to go through each line adding
them up manually, if a formula can be created to work it out
automatically.

[image: http://www.adrenaline-shots.co.uk/sheet/spreadsheet2.gif]

Any ideas would be greatly appreciated.

Cheers,
Jonathan

 

[Frank Kabel]

Hi
use
=SUMIF(C:C,"AB",B:B)

 

[jonnybump]

Thanks for replying so quickly!

That formula does appear to work, but it still requires me to manuall
type in the two-letter code to work it out - is it possible to alte
this formula so that, for example, it will only look for the two-lette
code in the adjacent cell, and will also only work it out the one time?

Take the following as an example (bear in mind I have only used a SU
formula to work this out!) :

[image: http://www.adrenaline-shots.co.uk/sheet/spreadsheet3.gif]

Thanks,
Jonatha

 

[jonnybump]

I think I've sorted it! By playing about with your original suggestion
I've come up with the formula:

*=IF(C2=C1,"",SUMIF(C:C,C2,B:B))*

Looking good at the moment - thanks very much matey!

Jonatha