SUMIFS function used with LEFT

Dear all.

I am trying to combine a SUMIFS function with a LEFT function to Sum the weighs of different loads of a batch.

Here the example:

"Sheet 1"
Column A Column B
Batch number Total Weight of Batch
220X5A
2201RA
22083A

"Sheet 2"
Column J Column K
Batch number per Load Weight of load
220X5A 3192
2201RA 3950
2201RA 3810
22083A 1874
22083A 1656
2201RAA 2652

What I am trying to do is to sum the weights of the different loads on sheet 2 (column K) into column B on sheet 1. The criteria is to match the batch number in column A on sheet one with the first six characters of the batch number in column J on sheet 2.

Hence, for Batch 2201RA the total weight (In column B, sheet 1) should be 10412, For batch 22083A, the total weight (In column B, sheet 1), should be 3530, etc.

I do not want to use a SUMPRODUCT formulae as it is heavy. I would prefer going the SUMIFS function.

What I Tried is the following: =SUMIFS('Sheet2'!K:K,(LEFT('Sheet2'!J:J,6),'Sheet1'!A:A)), but it give me an error

Please, I need help :-)

What version of Excel are you using? 2102 is just a build version.
If you have 365 or 2021 try

Formula:

Please Login or Register  to view this content.

You guys are the best!! Both worked - THANK YOU!!!!

Both worked

there were 3 solutions offered.

HAHAHA - All three worked - I ended up using the SUMPRODUCT one from Glenn :-)

Glad to help & thanks for the feedback.