danhendo888
Board Regular
- Joined
- Jul 15, 2019
- Messages
- 167
- Office Version
- 365
- Platform
- Windows
Cannot seem to make the below formula work.
I'm trying to SUM for values using two criteria
One of the criteria is in one cell but separated by a comma. I used Textsplit but it doesn't quite work.
I'm trying to SUM for values using two criteria
One of the criteria is in one cell but separated by a comma. I used Textsplit but it doesn't quite work.
| direct operator.xlsx | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Data: | |||||||||||
| 2 | Criteria2 | Criteria1 | USD | |||||||||
| 3 | Tesla | WHEEL | 5 | |||||||||
| 4 | Ford | OIL | 10 | |||||||||
| 5 | Ford | TYRE | 15 | |||||||||
| 6 | Tesla | TYRE | 20 | |||||||||
| 7 | Tesla | DEALER | 25 | |||||||||
| 8 | Tesla | OIL | 30 | Criteria1 | Criteria2 | Attempt1 | Attempt2 | Correct Output Should Be: | ||||
| 9 | Tesla | GAS | 35 | OIL, GAS | Tesla | 30 | 30 | 65 | ||||
| 10 | Ford | WINDOW | 15 | TYRE, WINDOW | Ford | 15 | 30 | |||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G9:G10 | G9 | =MAP(F9:F10,E9:E10,LAMBDA(make,code,SUM(SUMIFS(C3:C10,A3:A10,make,B3:B10,TEXTSPLIT(code,","))))) |
| H9 | H9 | =SUM(SUMIFS(C3:C10,A3:A10,F9,B3:B10,TEXTSPLIT(E9,","))) |
| Dynamic array formulas. | ||
Last edited by a moderator:
