+ Reply to Thread
Results 1 to 7 of 7

Combining SUMIFs and LEFT Functions

  1. #1
    Registered User
    Join Date
    08-24-2017
    Location
    WI, USA
    MS-Off Ver
    2010
    Posts
    3

    Combining SUMIFs and LEFT Functions

    Hello,

    I am trying to combine the SUMIFs and LEFT functions to sum data based upon two separate criteria.

    Column B Column C Column Z
    1. 10001 4001 1,000,000
    2. 10001 4004 1,000,000
    3. 52001 4099 5,000,000
    4. 62001 4512 6,000,000

    I am trying to return a sum of Column Z if Column B = 10001 and if LEFT Column C = 40. In this case, I should return a value of $2,000,000 for rows 1 and 2. The following formula is what I'm trying to use:
    =SUMIFS(Z1:Z4, C1:C4, LEFT(C1:C4, 2)="40", B1:B4, B1:B4="10001").

    The formula is returning a value of 0. I've tried using a SUMPRODUCT formula and a SUM/IF array combination without any success and I'm out of ideas. Any help is greatly appreciated! Thank you very much in advance.
    Last edited by Jawshaw; 08-24-2017 at 11:15 AM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combining SUMIFs and LEFT Functions

    You can't manipulate the values in the criteria range like that.
    Sumifs can only evalute that values exactly as they appear in the cells.

    Will that value in column C always be 4 digits?
    If YES, then you can use 2 criteria on column C
    >=4000 and <=4099

  3. #3
    Registered User
    Join Date
    08-24-2017
    Location
    WI, USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Combining SUMIFs and LEFT Functions

    Thank you for the response. Unfortunately many of the numbers in Column C are six digit numbers. The example I provided was for simplification purposes only. Should I revisit the use of SUMPRODUCT or array formulas? Any thoughts on how I could structure those?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combining SUMIFs and LEFT Functions

    Yep, either sumproduct. Or a helper column to extract the left 2. And use that column in sumifs.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combining SUMIFs and LEFT Functions

    Try

    =SUMPRODUCT(Z1:Z4, --(LEFT(C1:C4, 2)="40"), --(B1:B4=10001))

  6. #6
    Registered User
    Join Date
    08-24-2017
    Location
    WI, USA
    MS-Off Ver
    2010
    Posts
    3

    Re: Combining SUMIFs and LEFT Functions

    Thank you so much, Jonmo! This worked!! I have spent a significant amount of time trying to Google search and try many different variations of this formula. You have saved me an incredible amount of time and I really appreciate it! Cheers!

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combining SUMIFs and LEFT Functions

    You're welcome.

+ 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. [SOLVED] Sumifs with =left() criteria
    By rs1aj in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2015, 03:56 PM
  2. sumifs with left / sum product
    By papasmurfuo9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-30-2014, 11:39 AM
  3. Combining left and right functions
    By cmb80 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-01-2013, 05:48 AM
  4. [SOLVED] Combining left and right functions
    By cmb80 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-01-2013, 04:53 AM
  5. Formula Combining the SUMIF and LEFT functions to calculate totals
    By The_Snook in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-29-2013, 01:28 PM
  6. [SOLVED] For Each Cell - combining Left and If functions
    By Staalanden in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2012, 09:43 AM
  7. Replies: 0
    Last Post: 11-15-2007, 05:24 AM

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