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!

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!

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.

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.

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

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!

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.