+ Reply to Thread
Results 1 to 4 of 4

SUMIFS with multiple sum columns and multiple ORs

  1. #1
    Registered User
    Join Date
    09-14-2016
    Location
    England
    MS-Off Ver
    2013
    Posts
    8

    SUMIFS with multiple sum columns and multiple ORs

    Hello,

    I am trying to sum two columns where multiple AND/OR criteria is met. Currently, the SUMIFS formulae I'm using contain excessive repetitions of syntax and I'm certain there must be a more concise method.

    Please see attached sample data.

    I want to sum the AMOUNT1 and AMOUNT2 columns where
    TYPE = "a" or "b", and
    CHANNEL = "y" or "z", and
    COUNTRY = "UK" or "GB"

    In the sample data I attach, by filtering for the above, the sum comes to 40.45.

    Thanks,

    Katie
    Attached Files Attached Files

  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,604

    Re: SUMIFS with multiple sum columns and multiple ORs

    You can do it like this:

    =SUMPRODUCT(((A2:A26="a")+(A2:A26="b"))*((B2:B26="y")+(B2:B26="z"))*((E2:E26="UK")+(E2:E26="GB")),C2:C26+D2:D26)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    09-14-2016
    Location
    England
    MS-Off Ver
    2013
    Posts
    8

    Re: SUMIFS with multiple sum columns and multiple ORs

    Pete,

    That worked great. Had a little confusion since it errored when used in my real data but found that it was because I had a few #N/As in the columns to sum. Removed the #N/As and the formula worked a dream.

    Many thanks,

    Katie

  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,604

    Re: SUMIFS with multiple sum columns and multiple ORs

    Glad you got it working, Katie.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly 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. SUMIFS Multiple Columns
    By nathanexcelhelp in forum Excel General
    Replies: 6
    Last Post: 09-22-2016, 06:46 AM
  2. Sumifs with or statement over multiple columns
    By sjennings007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-07-2015, 11:05 PM
  3. [SOLVED] SUMIFS/AVERAGEIFS across multiple columns
    By keith740 in forum Excel General
    Replies: 30
    Last Post: 06-25-2015, 01:25 PM
  4. SUMIFs across multiple columns (SUMPRODUCT?)
    By source in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 01-26-2015, 02:39 PM
  5. Can I use sumifs on a range with multiple columns?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2014, 03:11 AM
  6. Multiple Criteria Sumifs in Multiple Columns
    By arowberry in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2014, 06:06 PM
  7. Sumifs with multiple criteria in multiple columns
    By Ganesh7299 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-30-2013, 01:30 AM

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