hello again,

i have two reference columns A & B
column A contains text, column B contains numbers

i also have another two columns C & D
column C contains text, column D contains the sum formula i am looking for.

C look-up for A, only for the 2 digit prefix.

while D sums up for B

e.g
A B C D
12 5 12 21
12A 10 13 54
12B 5
12C 1
13 6
13.01 3
13.02 45

If theres a formula available for Column D, please share.

--
regards,

Try this in D1:

=SUMPRODUCT((LEFT(A$1:A$7,2)=C1&"")*(B$1:B$7))

Copy into D2.

Hope this helps.

Pete

On May 19, 9:11*am, driller wrote:
hello again,

i have two reference columns A & B
column A contains text, column B contains numbers

i also have another two columns C & D
column C contains text, column D contains the sum formula i am looking for..

C look-up for A, only for the 2 digit prefix.

while D sums up for B

e.g
A * * * B * * * C * * * D
12 * * *5 * * * 12 * * *21
12A * * 10 * * *13 * * *54
12B * * 5 * * * * * * *
12C * * 1 * * * * * * *
13 * * *6 * * * * * * *
13.01 * 3 * * * * * * *
13.02 * 45 * * * * * * *

If theres a formula available for Column D, please share.

--
regards,

thanks for your reliable formula.
--
regards,



"Pete_UK" wrote:

Try this in D1:

=SUMPRODUCT((LEFT(A$1:A$7,2)=C1&"")*(B$1:B$7))

Copy into D2.

Hope this helps.

Pete

On May 19, 9:11 am, driller wrote:
hello again,

i have two reference columns A & B
column A contains text, column B contains numbers

i also have another two columns C & D
column C contains text, column D contains the sum formula i am looking for..

C look-up for A, only for the 2 digit prefix.

while D sums up for B

e.g
A B C D
12 5 12 21
12A 10 13 54
12B 5
12C 1
13 6
13.01 3
13.02 45

If theres a formula available for Column D, please share.

--
regards,

Another way

=SUMIF(A1:A7,"12*",B1:B7)+SUMIF(A1:A7,12,B1:B7)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"driller" wrote in message
...
hello again,

i have two reference columns A & B
column A contains text, column B contains numbers

i also have another two columns C & D
column C contains text, column D contains the sum formula i am looking
for.

C look-up for A, only for the 2 digit prefix.

while D sums up for B

e.g
A B C D
12 5 12 21
12A 10 13 54
12B 5
12C 1
13 6
13.01 3
13.02 45

If theres a formula available for Column D, please share.

--
regards,

thanks for the another way. Pete's formula works already.
--
regards,



"Bob Phillips" wrote:

Another way

=SUMIF(A1:A7,"12*",B1:B7)+SUMIF(A1:A7,12,B1:B7)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"driller" wrote in message
...
hello again,

i have two reference columns A & B
column A contains text, column B contains numbers

i also have another two columns C & D
column C contains text, column D contains the sum formula i am looking
for.

C look-up for A, only for the 2 digit prefix.

while D sums up for B

e.g
A B C D
12 5 12 21
12A 10 13 54
12B 5
12C 1
13 6
13.01 3
13.02 45

If theres a formula available for Column D, please share.

--
regards,

You're welcome - thanks for feeding back.

Pete

On May 19, 9:47*am, driller wrote:
thanks for your reliable formula.
--
regards,