Using LEFT() within a SUMPRODUCT()

[jbrouse]

Hello, everyone.

Can someone please assist me with using the LEFT() function with the following SUMPRODUCT() function?

=SUMPRODUCT('Sheet1'!$A$2:$A$100='Sheet2'!$L$5),--('Sheet1!$D$2:$D$100='Sheet2'!$F1),--('Sheet1'!$G$2:$G$100)

I want one of the conditions to check if the first 12 characters of each cell in a column match my lookup value (Sheet2, cell F1, for example).

Is this possible? How would the formula read?

Thanks in advance.

 

[Jonmo1]

Try

=SUMPRODUCT('Sheet1'!$A$2:$A$100='Sheet2'!$L$5),--(LEFT('Sheet1!$D$2:$D$100,12)='Sheet2'!$F1),--('Sheet1'!$G$2:$G$100)

 

[Yahya]

try this

=SUMPRODUCT(--(Sheet1!$A$2:$A$100=Sheet2!$L$5),--(LEFT(Sheet1!$D$14:$D$100,12)=$F$1),Sheet1!$G$2:$G$100)

 

[jbrouse]

It doesn't seem to work. It gives me a result of 0 when the result should be 1500.

 

[Jonmo1]

What is the lookup value in Sheet2 F1 ?
And what type of data is in Sheet1 Column D ?

 

[jbrouse]

The lookup value in sheet2 F1 is a alpha numeric part no. AW123456-789

Sheet1, column D has similar part nos, only a longer string - AW123456-78901A.

Thanks for your assistance!

 

[Jonmo1]

If this get's a correct result
=SUMPRODUCT('Sheet1'!$A$2:$A$100='Sheet2'!$L$5),--('Sheet1!$D$2:$D$100='Sheet2'!$F1),--('Sheet1'!$G$2:$G$100)
Given a complete part number in Sheet2 F1


Then I see no reason for this not to work
given first 12 digits of the same part number used in previous formula.
=SUMPRODUCT('Sheet1'!$A$2:$A$100='Sheet2'!$L$5),--(LEFT('Sheet1!$D$2:$D$100,12)='Sheet2'!$F1),--('Sheet1'!$G$2:$G$100)

 

[jbrouse]

I'm an idiot. I was looking for 12 characters, but my part no. in Sheet2, F1 was 13 characters, so it was saying if these 12 characters are the same as these 13 characters, sum it up! I'm fairly confident that won't ever happen.

It works now.

Thanks to all of you for your help!

 

[Jonmo1]

Glad to help, thanks for the feedback..

Side note, this isn't really a problem, but is not necessary..

=SUMPRODUCT(--('Sheet1'!$A$2:$A$100='Sheet2'!$L$5),--(LEFT('Sheet1!$D$2:$D$100,12)='Sheet2'!$F1),--('Sheet1'!$G$2:$G$100))

Should just be
=SUMPRODUCT(--('Sheet1'!$A$2:$A$100='Sheet2'!$L$5), --(LEFT('Sheet1!$D$2:$D$100,12)='Sheet2'!$F1),'Sheet1'!$G$2:$G$100)


You only need to do the --(...) on parts that have an expression (is colA = to "xyz")
The range that is being summed does not need that syntax.

 

[jbrouse]

My actual formula does not have that syntax. I was trying to figure out why it has it in my post. I think it is because my actual formula is a little different than what I posted (nested if), and I modified it to make it easier to understand.

Thanks again!