+ Reply to Thread
Results 1 to 8 of 8

SUMIFS function used with LEFT

  1. #1
    Registered User
    Join Date
    02-18-2022
    Location
    Pretoria, South Africa
    MS-Off Ver
    2102
    Posts
    3

    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 :-)

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    14,893

    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: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: SUMIFS function used with LEFT

    In Cell B2 of Sheet 1-
    Please Login or Register  to view this content.
    Last edited by sourabhg98; 02-18-2022 at 09:42 AM.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,513

    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.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

  5. #5
    Registered User
    Join Date
    02-18-2022
    Location
    Pretoria, South Africa
    MS-Off Ver
    2102
    Posts
    3

    Re: SUMIFS function used with LEFT

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

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    14,893

    Re: SUMIFS function used with LEFT

    Both worked
    there were 3 solutions offered.

  7. #7
    Registered User
    Join Date
    02-18-2022
    Location
    Pretoria, South Africa
    MS-Off Ver
    2102
    Posts
    3

    Re: SUMIFS function used with LEFT

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

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    14,893

    Re: SUMIFS function used with LEFT

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Right function, Left function, Mid function to extract values in column A
    By bjnockle in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-14-2020, 03:14 PM
  2. [SOLVED] sumifs formula to extract first left item based on Crteria
    By Howardc1001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2019, 01:20 AM
  3. [SOLVED] Combining SUMIFs and LEFT Functions
    By Jawshaw in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-24-2017, 11:20 AM
  4. Displaying wrong result on SUMIFS due to space in left
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-14-2016, 10:00 PM
  5. [SOLVED] Sumifs with =left() criteria
    By rs1aj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2015, 03:56 PM
  6. sumifs with left / sum product
    By papasmurfuo9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2014, 11:39 AM
  7. SUMIFS Formula with LEFT Function
    By Sultix in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-18-2009, 06:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1