SUMIF based on partial number match

[charliefth]

I'm sure this is a relatively simple problem, but I am trying to SUMIF based on the first 4 digits of a number.
I have attached a picture of a simplified version of this problem.
The formula I have tried is shown in the formula bar and gives me an answer of 0.

This formula works fine when the "Lot Number" contains a letter, but doesn't work when they are just numerical.
So I'm assuming the concatenate &"*" function isn't meant to be used with numbers.

How do I change the formula to make it work with numerical values?

 

[mrshl9898]

Are they always 6 digit?

=SUMIFS(B2:88,A2:A8,">="&D3*100,A2:A8,"<="&(D3*100)+100)

 

[jasonb75]

It would be easier with sumproduct than sumif

=SUMPRODUCT((--(LEFT($A$2:$A$8,4))=D3)*$B$2:$B$8)

edit:- here's a variation to the formula above that will work with text or numbers, any number of characters.

=SUMPRODUCT($B$2:$B$10*(LEFT($A$2:$A$10,LEN(D3))=D3&""))

 

[charliefth]

Are they always 6 digit?

=SUMIFS(B2:88,A2:A8,">="&D3*100,A2:A8,"<="&(D3*100)+100)

Thanks, this works!

I shortened the number for simplicity, they are actually always 8 digits.

For this would I use the following formula?

=SUMIFS(B2:B8,A2:A8,">="&D3*10000,A2:A8,"<="&(D3*10000)+10000)

 

[charliefth]

It would be easier with sumproduct than sumif

=SUMPRODUCT((--(LEFT($A$2:$A$8,4))=D3)*$B$2:$B$8)

edit:- here's a variation to the formula above that will work with text or numbers, any number of characters.

=SUMPRODUCT($B$2:$B$10*(LEFT($A$2:$A$10,LEN(D3))=D3&""))

Thanks, this works too!
However, I'm more au fait with SUMIFS so would rather use a complicated solution that I understand better

 

[jasonb75]

Using that method, I would personally add 1 to D3 before multiplying.

=SUMIFS(B2:B8,A2:A8,">="&D3*10000,A2:A8,"<"&(D3+1)*10000)

Although I should point out that this method (done either way) will give you an error with a lot number that contains a letter. The second method that I suggested in my previous reply will work with any format and any number of characters.