# Summing data based on multiple criteria

1. ## 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. ## 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}))

3. ## 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.

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. ## 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. ## 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.

6. ## 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. ## 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. ## Re: Summing data based on multiple criteria

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

9. ## 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.

10. ## Re: Summing data based on multiple criteria

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

11. ## 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:
`Please Login or Register  to view this content.`

12. ## 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. ## Re: Summing data based on multiple criteria

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

14. ## Re: Summing data based on multiple criteria

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

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

Thanks again everyone!

15. ## 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.

16. ## Re: Summing data based on multiple criteria

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

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