+ Reply to Thread
Results 1 to 8 of 8

SUMIF positive numbers with multiple criteria

  1. #1
    Registered User
    Join Date
    02-12-2020
    Location
    Michigan, USA
    MS-Off Ver
    16.0.12430.20184
    Posts
    4

    SUMIF positive numbers with multiple criteria

    I need help with the following formula.

    =SUMIFS(Hours_RAW!G:G,Hours_RAW!E:E,F325,Hours_RAW!B:B,G325)

    The Hours_Raw is a different sheet within the same workbook. Right now, it's set to sum all amounts in that column, which isn't the accurate number I need. I need to sum the amounts in Column G of that sheet, while still being able to only have it pull the amounts when the name in column E is the same as in F325 and when the date in column B is the same as in G325

    Any help would be appreciated. I'm not advanced with formulas as i'm still learning more than just the basic ones.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: SUMIF positive numbers with mulitple criteria.

    That should do what you describe, unless the value in F325 is not found in column E OR if the date is G325 is not found in column B. There are many reasons why the names might not match, although they may look the same - you could have leading or trailing spaces or other non-printable characters in F325 or in column E values. The date in G325 might be a text value that looks like a date, whereas column B contains proper dates (or vice versa), or it might be that the dates in column B are actually date/times which are formatted to show only the date part.

    It is impossible for us to know which of these (or something else) is the case without seeing your data, so please upload a sample Excel workbook as described in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-12-2020
    Location
    Michigan, USA
    MS-Off Ver
    16.0.12430.20184
    Posts
    4

    Re: SUMIF positive numbers with mulitple criteria.

    I should have clarified a little better in my original post. The formula in it's current state works. I just need to modify it or add to it in some way so that any negative numbers aren't calculated in that sum. I'm thinking a ">0" needs be added in somewhere but each time I try to add it in, I get an error. It's very possible i'm not adding in the right spot or don't have all the right quotations, commas, etc within the formula.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: SUMIF positive numbers with mulitple criteria.

    Okay, try it this way:


    =SUMIFS(Hours_RAW!G:G,Hours_RAW!E:E,F325,Hours_RAW!B:B,G325,Hours_RAW!G:G,">0")

    (changes in red).

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    02-12-2020
    Location
    Michigan, USA
    MS-Off Ver
    16.0.12430.20184
    Posts
    4

    Re: SUMIF positive numbers with multiple criteria

    I've uploaded the excel file in question. The formula is in Column D on the "safety_summary" tab.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    02-12-2020
    Location
    Michigan, USA
    MS-Off Ver
    16.0.12430.20184
    Posts
    4

    Re: SUMIF positive numbers with mulitple criteria.

    Thank you Guru, that did it! Much appreciation for your help! You've made my life much easier now.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,656

    Re: SUMIF positive numbers with multiple criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: SUMIF positive numbers with multiple criteria

    Glad to help.

    In addition to marking the thread as SOLVED, you might also like to know that you can show your appreciation directly and thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. SumIf including both positive and negative numbers
    By Schmaltzy72 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2019, 12:08 PM
  2. Sum of both a) Sumif with multiple criteria + b) positive totals
    By tsnguyen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-25-2019, 12:35 AM
  3. [SOLVED] Need help incorporating SUMIF to only calculate positive numbers in the attached sheet
    By spittingfire in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-13-2014, 12:39 PM
  4. [SOLVED] SUMIF positive and negative numbers - take double a negative number?
    By Zordrail in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2014, 08:34 AM
  5. [SOLVED] Deleting Correlating Positive and Negative Numbers based on Criteria
    By amaan.khowaja in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-02-2013, 06:49 AM
  6. Replies: 10
    Last Post: 03-10-2010, 04:23 PM
  7. adding positive and negative numbers all as positive
    By tomvh444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2009, 04:08 PM

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