conditional formula

[driller]

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.

 

[Pete_UK]

Try this in D1:

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

Copy into D2.

Hope this helps.

Pete

 

[driller]

thanks for your reliable formula.
--
regards,

Try this in D1:

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

Copy into D2.

Hope this helps.

Pete

 

[Bob Phillips]

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]

thanks for the another way. Pete's formula works already.
--
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)

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete