+ Reply to Thread
Results 1 to 11 of 11

SUMIFS - Multiple Criteria within a single critera

  1. #1
    Registered User
    Join Date
    10-05-2018
    Location
    usa
    MS-Off Ver
    2013
    Posts
    9

    SUMIFS - Multiple Criteria within a single critera

    Hey all,

    Running into issues with sumifs on multiple criteria.

    Trying to do it all within one single sumifs formula instead of having a single sumifs formula for each different string of the criteria.

    For example, assume column A has the status of a project (open, closed, in progress), column B has the product type (product1, product2), and column C has the office assigned to (New York, California) and column D is total revenue.

    This formula does not work for, and I do not understand why...=sum(sumifs($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1","product2"},$C:$C,{"New York","California"}))...anything I am missing here as to why it wouldn't be working?
    Last edited by nymetsfan25; 07-18-2019 at 12:28 PM.

  2. #2
    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
    45,436

    Re: SUMIFS - Multiple Criteria within a single critera

    Welcome to the forum.

    Untested, but you could try this:

    =SUM(SUMIFS($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1","product2"},$C:$C,{"New York","California"}))
    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.

  3. #3
    Registered User
    Join Date
    10-05-2018
    Location
    usa
    MS-Off Ver
    2013
    Posts
    9

    Re: SUMIFS - Multiple Criteria within a single critera

    I have the sum( in front of the sumifs in my file and it still doesn't work...left that out but will edit. Thanks for pointing that out.

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,088

    Re: SUMIFS - Multiple Criteria within a single critera

    you might want to try sum in front of it, like... ...=sum(sumifs($D:$D,$A:$A,{"Open","Closed","In Progress"},$B:$B,{"product1";"product2"},$C:$C,{"New York";"California"}))
    you may also have to add semicolons to the second and third sets.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    10-05-2018
    Location
    usa
    MS-Off Ver
    2013
    Posts
    9

    Re: SUMIFS - Multiple Criteria within a single critera

    Thanks - I did have the sum( in front of sumifs(.

    Interesting on the semi-colons on criteria after the first. Will try it out. Why would there be a distinction in the format between the 1st set of criteria and the ones following it?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,551

    Re: SUMIFS - Multiple Criteria within a single critera

    Hello and welcome to the forum.

    I can't help thinking that you might be better off using a Pivot Table.

    But what permuations are you tring to sum. It's not clear to me. Upload a workbook with some manually calculated results.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Registered User
    Join Date
    10-05-2018
    Location
    usa
    MS-Off Ver
    2013
    Posts
    9

    Re: SUMIFS - Multiple Criteria within a single critera

    I'm not sure if the attachment is coming through...but hopefully you can see attached.

    With these formulas and criteria, I should (I thought at least) be able to tie back to the sum total of the example revenue. Yes, for something like this, I could use a pivot table but this is obviously a very simple example...what I need this for goes far beyond a pivot table so that won't make sense

    Any thoughts as to what is wrong here?
    Attached Files Attached Files

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,181

    Re: SUMIFS - Multiple Criteria within a single critera

    {"In Progress","Open","Closed"} and {"Product1";"Product2"} give 2 dimensions array with 3x2 = 6 AND condition

    Excel and do only support 2 dimensions so, more or condition need to find other ways.

    1. Normal plus
    =SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,{"Product1";"Product2"},'data set'!$C:$C,"New York"))
    +SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,{"Product1";"Product2"},'data set'!$C:$C,"California"))

    2. Wildcard
    =SUM(SUMIFS('data set'!$D:$D,'data set'!$A:$A,{"In Progress","Open","Closed"},'data set'!$B:$B,"Product*",'data set'!$C:$C,{"New York";"California"}))

  9. #9
    Registered User
    Join Date
    10-05-2018
    Location
    usa
    MS-Off Ver
    2013
    Posts
    9

    Re: SUMIFS - Multiple Criteria within a single critera

    Understood...I think. Does it matter to be separated by comma vs. semi colon as suggested above?

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,181

    Re: SUMIFS - Multiple Criteria within a single critera

    Comma is for different column, semicolon for different row. to get 2 dimension 1 array must seperate by comma, the other must seperate by semicolon.

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,551

    Re: SUMIFS - Multiple Criteria within a single critera

    Thanks for uploading but you've not manually entered the results you expect to see.

+ 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. Replies: 0
    Last Post: 08-22-2017, 03:49 PM
  2. [SOLVED] SUMIFS (Several Critera)
    By mackan7695 in forum Excel General
    Replies: 5
    Last Post: 09-15-2015, 03:24 AM
  3. Sumifs criteria between multiple range in single cells
    By silambarasan.J in forum Excel General
    Replies: 3
    Last Post: 08-18-2015, 05:57 AM
  4. [SOLVED] SUMIFS w/ multiple criteria in a single column (using references to cells, not values)
    By akamenov88 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2015, 06:16 PM
  5. [SOLVED] SUMIFS with <> Critera
    By bdicarlo1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-20-2013, 01:35 PM
  6. [SOLVED] Sum Formula based on single or multiple critera that sums accross and array
    By Dial1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2013, 07:14 PM
  7. Replies: 16
    Last Post: 09-06-2005, 03: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