# SUMIFS function used with LEFT

1. ## 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
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

2. ## Re: SUMIFS function used with LEFT

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.`

3. ## Re: SUMIFS function used with LEFT

In Cell B2 of Sheet 1-
``Please Login or Register  to view this content.``

4. ## Re: SUMIFS function used with LEFT

Try:

=SUMPRODUCT((LEFT(Sheet2!\$J\$2:\$J\$7,6)=A2)*Sheet2!\$K\$2:\$K\$7)

but do not go mad and change to whole column references. Use something future-proof, but sensible.

5. ## Re: SUMIFS function used with LEFT

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

6. ## Re: SUMIFS function used with LEFT

Both worked
there were 3 solutions offered.

7. ## Re: SUMIFS function used with LEFT

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

8. ## Re: SUMIFS function used with LEFT

Glad to help & thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)