Hello guys, so I have a sheet with the following datasets:
The dataset on the left is updated daily and I would like to be able to dynamically update the dataset with the formula to account for any new clustomers which Individuals 1-3 worked with?
Lastly, if possible, would there be a way of seeing if any outside contributor(4-7) worked with Customer(A-C) on a separate list?
PERSONAL.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | ||||
Sheet3 |
PERSONAL.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Contributor | Customer | Amount | Customer | Profit | Individual 1 | Individual 2 | Individual 3 | |||
2 | Individual 1 | Client A | 100 | Client A | 930 | 100 | 130 | 450 | |||
3 | Individual 2 | Client B | 200 | Client B | 1950 | 150 | 200 | 1500 | |||
4 | Individual 3 | Client C | 300 | Client C | 650 | 250 | 100 | 300 | |||
5 | Individual 4 | Client A | 250 | ||||||||
6 | Individual 1 | Client B | 150 | ||||||||
7 | Individual 2 | Client C | 100 | ||||||||
8 | Individual 3 | Client A | 450 | ||||||||
9 | Individual 4 | Client B | 100 | ||||||||
10 | Individual 1 | Client C | 250 | ||||||||
11 | Individual 2 | Client A | 130 | ||||||||
12 | Individual 3 | Client B | 1500 | ||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F4 | F2 | =SUMIFS(Table1[Amount],Table1[Customer],E2) |
G2:I2,H3:I4 | G2 | =SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1) |
G3:G4 | G3 | =SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1) |
PERSONAL.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Contributor | Customer | Amount | Customer | Profit | Individual 1 | Individual 2 | Individual 3 | |||
2 | Individual 1 | Client A | 100 | Client A | 1030 | 100 | 130 | 450 | |||
3 | Individual 2 | Client B | 200 | Client B | 2300 | 150 | 200 | 1500 | |||
4 | Individual 3 | Client C | 300 | Client C | 773 | 250 | 100 | 300 | |||
5 | Individual 4 | Client A | 250 | Client D | 1000 | 200 | 0 | 300 | |||
6 | Individual 1 | Client B | 150 | Client E | 750 | 0 | 250 | 0 | |||
7 | Individual 2 | Client C | 100 | ||||||||
8 | Individual 3 | Client A | 450 | ||||||||
9 | Individual 4 | Client B | 100 | ||||||||
10 | Individual 1 | Client C | 250 | ||||||||
11 | Individual 2 | Client A | 130 | ||||||||
12 | Individual 3 | Client B | 1500 | ||||||||
13 | Individual 4 | Client C | 123 | ||||||||
14 | Individual 5 | Client A | 100 | ||||||||
15 | Individual 6 | Client E | 500 | ||||||||
16 | Individual 7 | Client D | 400 | ||||||||
17 | Individual 5 | Client B | 350 | ||||||||
18 | Individual 6 | Client F | 200 | ||||||||
19 | Individual 7 | Client G | 250 | ||||||||
20 | Individual 5 | Client F | 350 | ||||||||
21 | Individual 6 | Client G | 205 | ||||||||
22 | Individual 7 | Client D | 100 | ||||||||
23 | Individual 1 | Client D | 200 | ||||||||
24 | Individual 2 | Client E | 250 | ||||||||
25 | Individual 3 | Client D | 300 | ||||||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2:F6 | F2 | =SUMIFS(Table1[Amount],Table1[Customer],E2) |
G2:I2,H3:I6 | G2 | =SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1) |
G3:G6 | G3 | =SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1) |