I have the following data:
Data
| Data | |||
|---|---|---|---|
| Criteria A | Criteria B | Criteria C | Value |
| W10 | X50 | Jan | 14 |
| W10 | X51 | Jan | 12 |
| W10 | X53 | Jan | 10 |
| W20 | X51 | Jan | 10 |
| W20 | X50 | Feb | 20 |
| W20 | X50 | Feb | 12 |
| W20 | X50 | Feb | 19 |
| W20 | X51 | Mar | 11 |
| W20 | X51 | Mar | 14 |
| W30 | X51 | Apr | 11 |
And the following criteria (example):
Filter Conditions
| Criteria A | Criteria B | Criteria C |
|---|---|---|
| W10 | X50 | Jan |
| W20 | X51 | Feb |
| Mar |
The criteria lists are always the 3 listed, but there may be more or less values in them.
How can i make a formula summing up the Value column given this situation?
I have tried the following formula:
SUMPRODUCT(SUMIFS(value;'Criteria A from data;Criteria A from filter;'Criteria B from data;Criteria B from filter;'Criteria C from data;Criteria C from filter))
However, that only works with one column criteria.
In this case i would expect the sum to be 65
Expected result
Hindsholm is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
0
Here is an “old school method” that works in all versions.
=SUMPRODUCT(ISNUMBER(MATCH(A2:A11,H2:H9,0)*MATCH(B2:B11,I2:I9,0)*MATCH(C2:C11,J2:J9))*D2:D11)
1
Summing with
SUMPRODUCT = value_col * accumulated(COUNTIF(criteria, data)
by comparing columns in criteria with columns in data with same index; value_col is assumed to be the last column.
If columns are not contiguous, we could use COUNTIFS instead.
=LAMBDA(data, crit,
LET(
multiple_countif, LAMBDA(crit, src,
REDUCE(
EXPAND(1, ROWS(src), , 1),
SEQUENCE(COLUMNS(crit)),
LAMBDA(acc, n,
acc *
COUNTIF(INDEX(crit, , n), INDEX(src, , n))
)
)
),
SUMPRODUCT(TAKE(data, , -1), multiple_countif(crit, data))
)
)(A2:D11, F2:H4)