Lookup and Sum

[SMTheodore]

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

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP


"SMTheodore" <[email protected]> wrote in message
news:[email protected]...
> 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

[Duke Carey]

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