+ Reply to Thread
Results 1 to 7 of 7

SUMIFS and OR function

Hybrid View

bridge4444 SUMIFS and OR function 09-08-2014, 06:58 AM
Special-K Re: SUMIFS and OR function 09-08-2014, 07:02 AM
daddylonglegs Re: SUMIFS and OR function 09-08-2014, 07:02 AM
XOR LX Re: SUMIFS and OR function 09-08-2014, 07:05 AM
bridge4444 Re: SUMIFS and OR function 09-08-2014, 07:10 AM
bridge4444 Re: SUMIFS and OR function 09-08-2014, 07:11 AM
abousetta Re: SUMIFS and OR function 09-08-2014, 07:20 AM
  1. 09-08-2014, 06:58 AM #1
    bridge4444 is offline
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    56

    SUMIFS and OR function

    Can someone help with the following SUMIFS and OR formula.

    I am using the below formula to try and say sum column G if column M equals "Programme" or "Admin" and column F is greater than or equal to E5. The problem is with the OR section. I know I can fix the problem with getting rid of the OR and one of the criteria and duplicating the formula with the other criteria but hoping to use a simpler formula.

    =-SUMIFS('Source Data'!$G:$G,'Source Data'!$M:$M,OR("Programme","Admin"),'Source Data'!$F:$F,"<="&$E$5)

    Cheers
    Reply With QuoteRegister To Reply
  2. 09-08-2014, 07:02 AM #2
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,077

    Re: SUMIFS and OR function

    Does this work (untested)?

    =SUMIFS('Source Data'!$G:$G,AND('Source Data'!$F:$F,">="&$E$5,OR('Source Data'!$M:$M="Programme",'Source Data'!$M:$M="Admin")))

    or
    =SUMPRODUCT(('Source Data'!$F:$F,">="&$E$5)*('Source Data'!$M:$M="Programme"+'Source Data'!$M:$M="Admin")*('Source Data'!$G:$G))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 09-08-2014, 07:02 AM #3
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,697

    Re: SUMIFS and OR function

    Try this version

    =-SUM(SUMIFS('Source Data'!$G:$G,'Source Data'!$M:$M,{"Programme","Admin"},'Source Data'!$F:$F,"<="&$E$5))
    Audere est facere
    Reply With QuoteRegister To Reply
  4. 09-08-2014, 07:05 AM #4
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: SUMIFS and OR function

    Presume the minus sign at the beginning is intentional?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  5. 09-08-2014, 07:10 AM #5
    bridge4444 is offline
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: SUMIFS and OR function

    Yes. minus sign intentional.
    Reply With QuoteRegister To Reply
  6. 09-08-2014, 07:11 AM #6
    bridge4444 is offline
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    56

    Re: SUMIFS and OR function

    Thanks Daddylong legs. Your solution works.
    Reply With QuoteRegister To Reply
  7. 09-08-2014, 07:20 AM #7
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: SUMIFS and OR function

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

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Help nesting an OR function inside a SUMIFS function
    By wes228 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-21-2014, 06:22 PM
  2. [SOLVED] sumifs function
    By orshims in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2014, 10:56 AM
  3. SUMIFS Function
    By rjb59 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2013, 08:52 AM
  4. [SOLVED] NEED HELP! SUMIFS function
    By thedefense in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2012, 08:47 PM
  5. [SOLVED] SUMIFS function?
    By jed38 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2012, 04:33 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1