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!
Bookmarks