My goal is to sum all conditions where the index/match results correspond to the "age" being <0 or >3. I am able to do the formula for just one element at a time however I can not figure out how to combine this as one formula. My end result is summing all values instead of just items <0 and >3. The desired outcome of the below data (J3) is to show year 2023 / Type A as 8, not 12. Type B should be 7 in (J4). Thanks in advance!
| test.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | |||
| 1 | 2023 | 2024 | 2025 | 2023 | 2024 | |||||||
| 2 | Type | SubType | Quantity | Age | Age | Age | Type | SumType (<0 or >3) | ||||
| 3 | A | 1 | 4 | 0 | 1 | 2 | A | 12 | 12 | |||
| 4 | A | 2 | 2 | -1 | 0 | 1 | B | 19 | 19 | |||
| 5 | A | 3 | 3 | 4 | 5 | 6 | ||||||
| 6 | A | 4 | 3 | 4 | 5 | 6 | ||||||
| 7 | B | 1 | 5 | 5 | 6 | 7 | ||||||
| 8 | B | 2 | 8 | 2 | 3 | 4 | ||||||
| 9 | B | 3 | 6 | 1 | 2 | 3 | ||||||
Sheet1 (2) | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| J3:K4 | J3 | =SUM(IF($I3=$B3:$B9,IF(OR(INDEX($E3:$G9,0,MATCH(J$1,$E$1:$G$1,0))>3,INDEX($E3:$G9,0,MATCH(J$1,$E$1:$G$1,0))<0),$D3:$D9))) |
