Sum of duplicate values in a column

[John Young]

EG:

A B Result in C Answer =
1 xx1 5 =b1/(sum of all xx) 0.2777
2 xx2 6 =b2/(sum of all xx) 0.3333
3 ff1 10 =b3/(sum of all ff) 0.3333
4 xx2 3 =b4/(sum of all xx) 0.1666
5 ff3 8 =b5/(sum of all ff) 0.2666
6 ff2 12 =b6/(sum of all ff) 0.400
7 xx4 4 =b7/(sum of all xx) 0.2222

etc etc

What I am trying to do is have a macor that starts with
A1 & looks at all the codes down a1 to see if they match
only the 'text part of the string" then gets the value in
column B & sums it, & places the answer in c1 & so on.
The reason for the b colum values to be divided by this
is that they will be relative values against the total of
that same code

Hoepfully this makes sense.

John Young

 

[Bob Phillips]

John,

I would split the data up first, using Data>Text to columns which will put
it in 3 separate columns, and then use this formula

=C1/SUMPRODUCT(--(LEFT($B$1:$B$100,2)=LEFT(B1,2)),($C$1:$C$100))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Cecilkumara Fernando]

John Young,
If the 'text part of the string" is only the first two characters then
this formula will work
=B2/SUMPRODUCT((LEFT($A$2:$A$8,2)=LEFT(A2,2))*($B$2:$B$8))
HTH
Cecil