Lookup and Sum
 

Hi,

I am having trouble coming up with a formula for my data below. I need to
lookup the 1st thru 8th digits, and the 11th thru 13th digits (digits 9 & 10
are dynamic). If the 1st thru 8th digits, and the 11th thru 13th digits
match, I need to sum the col be values. Using the example below, cells
B1,B2,and B4 should be summed.

Col. A Col. B
L0234568AE0T1 492
L0234568AD0T1 300
L0334568AA0T1 100
L0234568AF0T1 50

Thanks in advance!
Suzanne

Lookup and Sum
 

=SUMIF(A1:A4,"L0234568??0T1",B1:B4)

HTH,
Bernie
MS Excel MVP


"SMTheodore" wrote in message
...
Hi,

I am having trouble coming up with a formula for my data below. I need to
lookup the 1st thru 8th digits, and the 11th thru 13th digits (digits 9 & 10
are dynamic). If the 1st thru 8th digits, and the 11th thru 13th digits
match, I need to sum the col be values. Using the example below, cells
B1,B2,and B4 should be summed.

Col. A Col. B
L0234568AE0T1 492
L0234568AD0T1 300
L0334568AA0T1 100
L0234568AF0T1 50

Thanks in advance!
Suzanne

Lookup and Sum
 

Use this formula in each row

=SUMPRODUCT(--(LEFT($A$1:$A$4,8)=LEFT(A1,8)),--(RIGHT($A$1:$A$4,3)=RIGHT(A1,3)),$B$1:$B$4)

The problem is you'll get the same sum on rows 1,2, and 4. The only way to
get around that is to generate a list of the unique combinations of first
8/last 3 characters and reference taht list in your formula



"SMTheodore" wrote:

Hi,

I am having trouble coming up with a formula for my data below. I need to
lookup the 1st thru 8th digits, and the 11th thru 13th digits (digits 9 & 10
are dynamic). If the 1st thru 8th digits, and the 11th thru 13th digits
match, I need to sum the col be values. Using the example below, cells
B1,B2,and B4 should be summed.

Col. A Col. B
L0234568AE0T1 492
L0234568AD0T1 300
L0334568AA0T1 100
L0234568AF0T1 50

Thanks in advance!
Suzanne