Sum of duplicate values in a column
 

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

Sum of duplicate values in a column
 

John,

I would split the data up first, using DataText 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)

"John Young" wrote in message
...
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

Sum of duplicate values in a column
 

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

"John Young" wrote in message
...
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