Hi all,
I have two sets of data. I want to multiply the data in one set by the data in the other if the store name matches. My issue is that one set of data will be missing some of the stores from the first set. I want to be able to calculate the total without calculating the individual items.
This will make more sense in the attachment. I am thinking maybe an array formula but I really don't know how to work with those that well.
I have two sets of data. I want to multiply the data in one set by the data in the other if the store name matches. My issue is that one set of data will be missing some of the stores from the first set. I want to be able to calculate the total without calculating the individual items.
This will make more sense in the attachment. I am thinking maybe an array formula but I really don't know how to work with those that well.
| Different Size Arrays.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 3 | Units Sold | ||||||||||||||
| 4 | |||||||||||||||
| 5 | Store | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | |||||||||
| 6 | Store 1 | 4 | 8 | 3 | 8 | 5 | |||||||||
| 7 | Store 2 | 4 | 5 | 8 | 4 | 3 | |||||||||
| 8 | Store 3 | 10 | 4 | 10 | 7 | 4 | |||||||||
| 9 | Store 4 | 9 | 1 | 10 | 5 | 9 | |||||||||
| 10 | Store 5 | 3 | 5 | 8 | 7 | 5 | |||||||||
| 11 | Store 6 | 7 | 7 | 2 | 5 | 2 | |||||||||
| 12 | Store 7 | 1 | 3 | 6 | 6 | 0 | |||||||||
| 13 | Store 8 | 2 | 4 | 5 | 4 | 2 | |||||||||
| 14 | Store 9 | 1 | 5 | 5 | 2 | 2 | |||||||||
| 15 | Store 10 | 7 | 9 | 10 | 7 | 10 | |||||||||
| 16 | |||||||||||||||
| 17 | Price per Unit | ||||||||||||||
| 18 | Store | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | |||||||||
| 19 | Store 1 | $ 23.00 | $ 27.00 | $ 27.00 | $ 28.00 | $ 26.00 | |||||||||
| 20 | Store 2 | $ 22.00 | $ 36.00 | $ 32.00 | $ 23.00 | $ 37.00 | |||||||||
| 21 | Store 3 | $ 40.00 | $ 40.00 | $ 36.00 | $ 25.00 | $ 33.00 | |||||||||
| 22 | Store 5 | $ 23.00 | $ 27.00 | $ 34.00 | $ 39.00 | $ 21.00 | |||||||||
| 23 | Store 6 | $ 39.00 | $ 24.00 | $ 22.00 | $ 25.00 | $ 34.00 | |||||||||
| 24 | Store 7 | $ 34.00 | $ 38.00 | $ 22.00 | $ 32.00 | $ 34.00 | |||||||||
| 25 | Store 9 | $ 30.00 | $ 28.00 | $ 34.00 | $ 22.00 | $ 32.00 | |||||||||
| 26 | Store 10 | $ 33.00 | $ 23.00 | $ 33.00 | $ 23.00 | $ 26.00 | |||||||||
| 27 | |||||||||||||||
| 28 | |||||||||||||||
| 29 | Intended Result | ||||||||||||||
| 30 | Store | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | |||||||||
| 31 | Store 1 | $ 92.00 | $ 216.00 | $ 81.00 | $ 224.00 | $ 130.00 | |||||||||
| 32 | Store 2 | $ 88.00 | $ 180.00 | $ 256.00 | $ 92.00 | $ 111.00 | |||||||||
| 33 | Store 3 | $ 400.00 | $ 160.00 | $ 360.00 | $ 175.00 | $ 132.00 | |||||||||
| 34 | Store 4 | #N/A | #N/A | #N/A | #N/A | #N/A | |||||||||
| 35 | Store 5 | $ 69.00 | $ 135.00 | $ 272.00 | $ 273.00 | $ 105.00 | |||||||||
| 36 | Store 6 | $ 273.00 | $ 168.00 | $ 44.00 | $ 125.00 | $ 68.00 | |||||||||
| 37 | Store 7 | $ 34.00 | $ 114.00 | $ 132.00 | $ 192.00 | $ - | |||||||||
| 38 | Store 8 | #N/A | #N/A | #N/A | #N/A | #N/A | |||||||||
| 39 | Store 9 | $ 30.00 | $ 140.00 | $ 170.00 | $ 44.00 | $ 64.00 | |||||||||
| 40 | Store 10 | $ 231.00 | $ 207.00 | $ 330.00 | $ 161.00 | $ 260.00 | |||||||||
| 41 | |||||||||||||||
| 42 | Total | $ 1,217.00 | $ 1,320.00 | $ 1,645.00 | $ 1,286.00 | $ 870.00 | <need to get this without calculating individual store data | ||||||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E31:I40 | E31 | =VLOOKUP($D31,$D$6:$I$15,COLUMNS($D$6:E$15),FALSE)*VLOOKUP($D31,$D$19:$I$26,COLUMNS($D$19:E$26),FALSE) |
| E42:I42 | E42 | =SUM(E31:E33,E35:E37,E39:E40) |
