+ Reply to Thread
Results 1 to 8 of 8

Sumifs again....with hard coded number range and additional criteria

  1. #1
    Forum Contributor
    Join Date
    02-13-2016
    Location
    BMass
    MS-Off Ver
    2010
    Posts
    154

    Sumifs again....with hard coded number range and additional criteria

    Hi, I am tying to do a sumifs formula that
    =Sumifs('sheet2'!$A$5 :$A$20,'sheet2'!$B$5:$B$20,Left($B9,4),'sheet2'!$C$5:$C$20,">59",'sheet2'!$C$5:$C$20,"<199",'sheet2'!$D$5:$D$20,'sheet3'!$C$11)*-1

    It is not returning the right totals.
    I need sheet 2 column a to sum if:
    The 4 digits on sheet 2 in column b match the left 4 digits of reference cell on results page cell b9
    Account reference numbers on sheet 2 column c are between 59 and 1999
    And
    If the period number column d on sheet 2 matches the number in cell C11 on sheet 3

    Please troubleshoot my formula so I can fix. Thank you!

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Sumifs again....with hard coded number range and additional criteria

    I don't see anything wrong with it out of context.

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  3. #3
    Registered User
    Join Date
    11-20-2015
    Location
    Rogers
    MS-Off Ver
    2010
    Posts
    22

    Re: Sumifs again....with hard coded number range and additional criteria

    Is the problem that you used >59 and <199 instead of >=59 and <=199?

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,700

    Re: Sumifs again....with hard coded number range and additional criteria

    It works for me based on junk info I put in a sheet using it. One thing I notice is that you write
    Account reference numbers on sheet 2 column c are between 59 and 1999
    but your formula has >59 and <199 - not 1999. could that be the problem?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Contributor
    Join Date
    02-13-2016
    Location
    BMass
    MS-Off Ver
    2010
    Posts
    154

    Re: Sumifs again....with hard coded number range and additional criteria

    Hi, Thanks for the responses. I have been trying different computers but the attach function is not working for me. I will post what I have and maybe someone can post it into excel.

    Example of problem: =SUMIFS(Sheet2!A5:A20,Sheet2!D5:D20,LEFT(Sheet1!B5,4),Sheet2!E5:E20,">59",Sheet2!E5:E20,"<199",Sheet2!C5:C20,Sheet3!C11)*-1


    Sheet 1

    (In cells B5 to B10)
    1111-Careforward
    1112-Health
    1113-Professional
    1114-List
    1114-Category
    1115-Assist

    Sheet 2

    Cell A5 to A20
    -$49
    -$75
    -$4,455
    -$529,456
    -$4,548
    -$52,665
    -$2,355
    -$5,565
    -$7,847
    -$15,649
    -$41,652
    -$285
    -$96
    -$74,255
    -$4,585,641
    -$545,456

    Cell C5 to C20
    1
    3
    3
    4
    5
    6
    3
    1
    2
    3
    4
    5
    6
    3
    1
    2

    Cell D5 to D20
    1111
    1112
    1112
    1111
    1113
    1114
    1115
    1112
    1113
    1115
    1114
    1112
    1111
    1112
    1112
    1115

    Cell e5 to e20
    108
    128
    114
    96
    45
    69
    204
    169
    199
    1423
    1586
    80
    70
    42
    61
    26


    Sheet 3

    In cell C11 the number "3"

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,700

    Re: Sumifs again....with hard coded number range and additional criteria

    ok, so plugging your numbers into my sheet, I get zero. Now looking at your formula you want D5:D20 to use the criteria on sheet 1 in B5 which the left four is 1111. So highlighting all the 1111s in sheet2 there are 3, now looking at the cell in sheet3 C11 is 3. Across from all the 1111s in sheet2 in column C there are no 3s so it appears it should fail or return zero. If on the other hand I pick 1 in C11 on sheet3 the formula returns 49 dollars.

  7. #7
    Forum Contributor
    Join Date
    02-13-2016
    Location
    BMass
    MS-Off Ver
    2010
    Posts
    154

    Re: Sumifs again....with hard coded number range and additional criteria

    Sambo kid,

    So it is working.... Thanks, I need to double check my results and put $$ around the ranges. I did this and 1112 set populated 4591206 so it may be my data. Thank you.
    Last edited by jeffreybrown; 11-09-2018 at 04:58 PM. Reason: Removed full quote which is not necessary!

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    Office 365 ProPlus - work; 2012 for Mac at home
    Posts
    6,700

    Re: Sumifs again....with hard coded number range and additional criteria

    I'd say more than kind of working, it is working, it is just that the criteria you picked (1111 and 3) didn't find anything to sum. I think you're good to go with it.
    AND, thank you for the rep!

+ 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. hard-coded vs ?
    By daulpuchich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2015, 10:19 AM
  2. Replies: 5
    Last Post: 08-21-2014, 09:10 AM
  3. [SOLVED] Excel VBA: changing hard coded column to dynamic range to autofill to last row
    By AishaSanz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-25-2014, 11:40 AM
  4. Replies: 6
    Last Post: 08-04-2013, 11:53 AM
  5. [SOLVED] Sumifs that includes additional criteria
    By Hesh in forum Excel General
    Replies: 9
    Last Post: 06-19-2012, 10:23 AM
  6. Hard coded number range into a function
    By bigdavediode in forum Excel General
    Replies: 9
    Last Post: 04-10-2009, 12:09 PM
  7. [SOLVED] hard coded text
    By William Benson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-06-2005, 02:05 PM

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