Hello,
I am trying to figure out a solution to this problem. In most of the online guides I've read regarding SUMPRODUCT with conditions, it assumes you know what criteria you'll provide. The problem is I don't. Rather than having "array = criteria1," it should be something like "array = {criteria1,criteria2, criteria_n}." The number of criteria inside {} is determined by a FILTER function. However, it will throw a #N/A error. Does anyone know the solution to this problem?
In the example below, different regions will use synonymous words. I'll need to map them to a single common word in order for me to analyze the data. The SUMPRODUCT table is what needs to be solved. It should match the Result table.
I am trying to figure out a solution to this problem. In most of the online guides I've read regarding SUMPRODUCT with conditions, it assumes you know what criteria you'll provide. The problem is I don't. Rather than having "array = criteria1," it should be something like "array = {criteria1,criteria2, criteria_n}." The number of criteria inside {} is determined by a FILTER function. However, it will throw a #N/A error. Does anyone know the solution to this problem?
In the example below, different regions will use synonymous words. I'll need to map them to a single common word in order for me to analyze the data. The SUMPRODUCT table is what needs to be solved. It should match the Result table.
| Book2.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | MappingTable | Sheet2/Region1 | Amount | Sheet3/Region2 | Amount | Sheet 4/Region3 | Amount | ||||||||
| 2 | Mapping | Region1 | Region2 | Region3 | Auto | 1 | Automobile | 2 | Vehicle | 5 | |||||
| 3 | Automobile | Auto | Vehicle | Car | 4 | Pop | 4 | Coke | 5 | ||||||
| 4 | Automobile | Car | Automobile | Soda | 7 | Biscuits | 7 | Cookies | 4 | ||||||
| 5 | Soda | Soda | Pop | Coke | Pop | 4 | Jet | 9 | Turboprop | 3 | |||||
| 6 | Soda | Pop | Cookies | 8 | Airplane | 10 | |||||||||
| 7 | Cookies | Biscuits | Cookies | Plane | 2 | Plane | 1 | ||||||||
| 8 | Airplane | Plane | Jet | Turboprop | Airplane | 5 | |||||||||
| 9 | Airplane | Airplane | |||||||||||||
| 10 | Airplane | Plane | |||||||||||||
| 11 | |||||||||||||||
| 12 | |||||||||||||||
| 13 | |||||||||||||||
| 14 | |||||||||||||||
| 15 | |||||||||||||||
| 16 | SUMPRODUCT | Region1 Amount | Region2 Amount | Region3 Amount | |||||||||||
| 17 | Automobile | #N/A | #N/A | #N/A | |||||||||||
| 18 | Soda | #N/A | #N/A | #N/A | |||||||||||
| 19 | Cookies | 0 | 7 | 4 | |||||||||||
| 20 | Airplane | #N/A | #N/A | #N/A | |||||||||||
| 21 | |||||||||||||||
| 22 | |||||||||||||||
| 23 | Result | Region1 Amount | Region2 Amount | Region3 Amount | |||||||||||
| 24 | Automobile | 5 | 2 | 5 | |||||||||||
| 25 | Soda | 11 | 4 | 5 | |||||||||||
| 26 | Cookies | 0 | 7 | 4 | |||||||||||
| 27 | Airplane | 7 | 20 | 3 | |||||||||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B17:B20 | B17 | =SUMPRODUCT($G$2:$G$14,--($F$2:$F$14=FILTER(B$3:B$10,$A$3:$A$10=$A17))) |
| C17:C20 | C17 | =SUMPRODUCT($J$2:$J$14,--($I$2:$I$14=FILTER(C$3:C$10,$A$3:$A$10=$A17))) |
| D17:D20 | D17 | =SUMPRODUCT($M$2:$M$14,--($L$2:$L$14=FILTER(D$3:D$10,$A$3:$A$10=$A17))) |
