+ Reply to Thread
Results 1 to 16 of 16

Summing data based on multiple criteria

  1. #1
    Registered User
    Join Date
    09-15-2017
    Location
    New York
    MS-Off Ver
    Pro Plus 2013
    Posts
    9

    Summing data based on multiple criteria

    Problem:
    Sum a specific column (Values to be Summed) based on matching 3 unique elements that are identified in 3 separate columns.

    example.jpg

    If I wanted to sum values based on:
    Criteria 1 = 10
    Criteria 2 = 28, 33 or 41
    Criteria 3 = 3, 4 or 5

    The logic should be:
    Criteria 1 = 10? IF “Yes” Then
    Criteria 2 = 28, 33 or 33 or 41? IF “Yes” Then
    Criteria 3 = 3 or 4 or 5? IF “YES” Then
    Sum = Row 6 + Row 9 + Row 10

    Based on the logic above:
    (Criteria 1) Select Rows 2, 3, 6, 9 and 10
    (Criteria 2) Select Rows 2, 6, 9 and 10 (Row 3 is dropped due to mismatch)
    (Criteria 3) Select Rows 6, 9 and 10 (Row 2 is dropped due to mismatch)
    Remaining Rows 6, 9, 10 are summed (Values to be Summed Column)
    (Row 6 + Row 9 + Row 10) = 13 + 38 + 75 = 126

    I tried:
    INDEX (array version)
    SUMIFS
    Combination of SUMIFS with OR and MATCH functions
    Combination of INDEX with OR and MATCH functions

    It is possible I implemented them incorrectly despite having used them successfully in the past.

    Any input is appreciated. Thanks!

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Summing data based on multiple criteria

    Try...

    =SUMPRODUCT(SUMIFS(D1:D10,A1:A10,10,B1:B10,{28,33,41},C1:C10,{3;4;5}))
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    09-15-2017
    Location
    New York
    MS-Off Ver
    Pro Plus 2013
    Posts
    9

    Re: Summing data based on multiple criteria

    Thanks for the response jeffreybrown. It was very helpful.

    Using the formula you provided the final sum is 113, not 126. The formula appears to exclude row 6 despite meeting the criteria for all 3 criteria columns.

    2 follow up questions:
    1. Why is row 6 being excluded?
    2. Is it possible to use this function with reference cells? ie. Instead of hardcoding criteria 1(10), criteria 2(28,33,41) etc. can I reference criteria 1(A1), criteria 2(B1,B2,B3), etc?

    I'm getting closer to the solution so I greatly appreciate your feedback. I will read up on the SUMPRODUCT function and play around with the formula more.

    Thanks!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Summing data based on multiple criteria

    Will you attach a sample file. When you paste a picture, we can't work on a picture and we are only left with the option of retyping your info.



    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    09-15-2017
    Location
    New York
    MS-Off Ver
    Pro Plus 2013
    Posts
    9

    Re: Summing data based on multiple criteria

    As requested I prepared an Excel spreadsheet with Before(what is happening now) and After(what I want to have happen) tabs.

    Thanks again for the help. I'll keep messing with the formula on my end and hopefully there is a breakthrough.
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Summing data based on multiple criteria

    Well first off, you did not use the formula posted in post #2

    This is what you used >> =SUMPRODUCT(SUMIFS(E2:E11,B2:B11,10,C2:C11,{28,33,41},D2:D11,{3,4,5}))

    Please check it again post #2. Make notice of the "," versus the ";"

    As for referencing ranges, normally yes this is doable, but since you have multiple criteria, this might take something else. Let me look.

  7. #7
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Summing data based on multiple criteria

    This array formula should do it. Must be array confirmed, using INDEX in place of CSE doesn't seem to work with TRANSPOSE (unless I tried it in the wrong place).

    =SUMPRODUCT(SUMIFS(E2:E11,B2:B11,10,C2:C11,H3:H5,D2:D11,TRANSPOSE(I3:I5)))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Summing data based on multiple criteria

    Thanks for the help Jason. From my test it seems to work good.

  9. #9
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Summing data based on multiple criteria

    You're welcome, Jeff!
    Thanks for the feedback and rep

    edit:-

    One point to add, I think that this is limited to 2 criteria ranges. I did figure out a method that works with more, but can't remember the functions used, or if it included helper columns.
    Last edited by jason.b75; 04-03-2019 at 03:05 PM.

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

    Re: Summing data based on multiple criteria

    @ ambadatexcel

    You've encountered a common problem. The OR criteria in multiple criteria COUNTIFS requires and returns 2D array.

    If you are interested for an explanation of why jason.b75's formula works. Check this link.

    COUNTIFS: Multiple “OR” criteria for one or two criteria_Ranges
    Dave

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

    Re: Summing data based on multiple criteria

    @ jason.b75

    This array formula should do it. Must be array confirmed, using INDEX in place of CSE doesn't seem to work with TRANSPOSE (unless I tried it in the wrong place).
    Compliments of Lori here.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Summing data based on multiple criteria

    Thanks, Dave!

    I have used that method a few times to make arrays work in places where they shouldn't, didn't even think to try it here.

    I'm gonna blame it on Wednesday

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

    Re: Summing data based on multiple criteria

    You're welcome, and thanks for the added rep.

  14. #14
    Registered User
    Join Date
    09-15-2017
    Location
    New York
    MS-Off Ver
    Pro Plus 2013
    Posts
    9

    Re: Summing data based on multiple criteria

    Jason.b75's formula solved it! Thank you Jason and Jefferybrown for doing the heavy lifting.

    Flameretired I'll definitely check out the reading you linked.

    I'm familiar with the transpose function but I never thought of using it for this type of function(doh!).

    Thanks again everyone!

  15. #15
    Registered User
    Join Date
    09-15-2017
    Location
    New York
    MS-Off Ver
    Pro Plus 2013
    Posts
    9

    Re: Summing data based on multiple criteria[Solved]

    Summary below in case anyone else has similar issue.

    Problem:
    Sum a specific column (Values to be Summed) based on matching 3 unique elements that are identified in 3 separate columns.

    Solution:
    =SUMPRODUCT(SUMIFS(E2:E11,B2:B11,10,C2:C11,H3:H5,D2:D11,TRANSPOSE(I3:I5)))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Read rest of the thread for more details.

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,069

    Re: Summing data based on multiple criteria

    ambadatexcel,

    Happy you now have a working solution. We are happy to help and thanks for the feedback.

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

+ 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. summing data based on criteria on multiple rows and columns
    By Andy N in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2017, 03:07 PM
  2. [SOLVED] Summing Data Based on multiple criteria that changes
    By aas72 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-14-2015, 05:18 PM
  3. Summing Based on Multiple Criteria
    By SJT in forum Excel General
    Replies: 4
    Last Post: 02-12-2015, 01:31 AM
  4. Help with summing based on multiple criteria
    By blaugrana9 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-15-2014, 03:50 AM
  5. [SOLVED] Help Summing Data from Table Based on Multiple Criteria
    By D. from So Cal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2014, 02:16 PM
  6. Summing based on multiple criteria
    By Environment in forum Excel General
    Replies: 2
    Last Post: 08-23-2011, 10:19 AM
  7. Summing Based on Multiple Criteria
    By SJT in forum Excel General
    Replies: 1
    Last Post: 09-23-2007, 01:34 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