+ 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
    Office 2019 (still learning)
    Posts
    24,598

    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
    Office 2019 (still learning)
    Posts
    24,598

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,335

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    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