Sumproduct with left function - numeric string

[Rodrigues]

Dear All
I'm wondering if anyone can help me.
I'm trying to extract a result using sumproduct with left function.
Column A Column B Column C Column D
1125001 OUT A 90
2125001 OUT A 2
15125001 OUT A 49
15125001 OUT A 6

M1-OUT
M2=A

Formula I'm using: =SUMPRODUCT((--(LEFT(A6:A23,1)="1")*(B6:B23=M1)*(C6:C23=M2)*(D6:D23)))
Aim: on cell J6 I should have 90 as a result and I have 145.
Attached is a file for better clarification.
Any help it will be much appreciated.
Many thanks in advance.
Regards
Rodrigues

 

[eibi]

Rodrigues,

Welcome to the forums.

In cell J6, try this formula:

=SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="1"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23)

And J8:

=SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="2"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23)

Is this what you're looking for?

All best.

 

[Khalid NGO]

Hi Rodrigues,
I didn't found any mistake in your formula, but not sure why the result is wrong
May be the "1"s are causing problem in array while converting into TRUE / FALSE.

I did try with the following too:
=SUM(IF((LEFT(A6:A23,1)+0)=1,IF(B6:B23=M1,IF(C6:C23=M2,D6:D23))))
but the result is same

as I thought (not sure) "1"s are causing problem in array while converting into TRUE / FALSE, can you use =LEFT(A6:A23,2) ?
so the criteria would be more than one character

something like:
=SUMPRODUCT(((LEFT(A6:A23,2)+0)=11)*(B6:B23=M1)*(C6:C23=M2)*(D6:D23))

or this with Ctrl+Shift+Enter:
=SUM(IF((LEFT(A6:A23,2)+0)=11,IF(B6:B23=M1,IF(C6:C23=M2,D6:D23))))

Regards,

 

[NARAYANK991]

Hi ,

One more suggestion :

=SUMPRODUCT((LEFT($A$6:$A$23,LEN($A$6:$A$23)-6)=I6&"")*($B$6:$B$23=$M$1)*($C$6:$C$23=$M$2)*($D$6:$D$23))

Narayan

 

[Rodrigues]

Rodrigues,

Welcome to the forums.

In cell J6, try this formula:

=SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="1"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23)

And J8:

=SUMPRODUCT((--(LEN(A6:A23)-(6)=1))*(--(LEFT(A6:A23,1)="2"))*(B6:B23=M1)*(C6:C23=M2)*D6:D23)

Is this what you're looking for?

All best.

Eibi
I have replied by email, but not sure if you have received. Did work , many thanks, much appreciated.
All the best.
Rodrigues

 

[Rodrigues]

Hi Rodrigues,
I didn't found any mistake in your formula, but not sure why the result is wrong
May be the "1"s are causing problem in array while converting into TRUE / FALSE.

I did try with the following too:
=SUM(IF((LEFT(A6:A23,1)+0)=1,IF(B6:B23=M1,IF(C6:C23=M2,D6:D23))))
but the result is same

as I thought (not sure) "1"s are causing problem in array while converting into TRUE / FALSE, can you use =LEFT(A6:A23,2) ?
so the criteria would be more than one character

something like:
=SUMPRODUCT(((LEFT(A6:A23,2)+0)=11)*(B6:B23=M1)*(C6:C23=M2)*(D6:D23))

or this with Ctrl+Shift+Enter:
=SUM(IF((LEFT(A6:A23,2)+0)=11,IF(B6:B23=M1,IF(C6:C23=M2,D6:D23))))

Regards,

Hi Khalid
Yes it's the "1", many thanks for your time and help.
Regards
Rodrigues