kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 922
- Office Version
- 365
Hi,
I have a data table as follows:
I need to summarize as follows:
The summary is based on the following criteria:
1) Sales manager ranked as BA are the group leaders and their down liners are BB and BC. Every sales manager are assigned a unit number and all sales under that unit number needs to be sum.
2) Only sales managers that their status is A which active needs to be summed. S stands for suspended and T stands for Terminated. Both S and T does not count as they are not active.
I tried to do this by pivot table but it is not able to sum in the way I wanted as per the above table. I need a formula solution. The reason is I have 5-6 reports to be prepared on daily basis and the rest of the reports I manage to formularize where I just copy and paste the raw data and the summary table populates automatically. For this report, I am currently using a combination of pivot table, vlookup and other manual methods.
Appreciate all the help on how I could formularize the table.
I have a data table as follows:
Excel Workbook | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | |||
3 | Unit | Rank | Name | Status | Sales ID | AP_OL | AP_ON_RP | AP_ANN_GOLD | SP_OL_AP | IL_TUP_AP | RTUP_AP_NEW | SP_IL_AP | ||
4 | 5 | BA | John | A | KL445877854 | 220 | 300 | 300 | 300 | 300 | 900 | 300 | ||
5 | 4 | BA | Mark | A | KL445877855 | 100 | 500 | 500 | 500 | 500 | 200 | 500 | ||
6 | 65 | BD | Omeara | A | KL445877856 | 600 | 400 | 400 | 400 | 400 | 500 | 400 | ||
7 | 65 | BA | Joil | A | KL445877856 | 600 | 400 | 400 | 400 | 400 | 500 | 400 | ||
8 | 23 | BC | Elaine | T | * | * | * | * | * | * | * | * | ||
9 | 23 | BA | Debbie | T | * | * | * | * | * | * | * | * | ||
10 | 87 | BB | Janice | A | KL445877848 | 800 | 500 | 300 | 300 | 500 | 400 | 500 | ||
11 | 87 | BA | Moty | A | KL445877848 | 800 | 500 | 300 | 300 | 500 | 400 | 500 | ||
12 | 2 | BC | Mike | S | * | * | * | * | * | * | * | * | ||
13 | 3 | BA | Pike | A | KL445877850 | 300 | 200 | 400 | 300 | 200 | 500 | 200 | ||
14 | 5 | BC | Norman | A | KL445877851 | 500 | 500 | 200 | 500 | 500 | 700 | 500 | ||
15 | 4 | BA | Mark | A | KL445877821 | 400 | 500 | 500 | 400 | 500 | 600 | 500 | ||
16 | 65 | BC | Julie | A | KL445877822 | 200 | 400 | 900 | 200 | 400 | 400 | 400 | ||
17 | 23 | BB | Nathan | T | * | * | * | * | * | * | * | * | ||
18 | 87 | BC | Nora | A | KL445877824 | 700 | 500 | 500 | 900 | 500 | 500 | 500 | ||
19 | 2 | BA | Kite | S | * | * | * | * | * | * | * | * | ||
20 | 3 | BB | Ranjit | A | KL445877847 | 200 | 200 | 400 | 500 | 600 | 200 | 600 | ||
21 | 5 | BC | Norman | A | KL445877848 | 500 | 500 | 500 | 500 | 500 | 500 | 500 | ||
22 | 4 | BB | Voight | A | KL445877849 | 500 | 500 | 500 | 400 | 400 | 400 | 400 | ||
23 | 65 | BD | Omeara | A | KL445877850 | 400 | 400 | 700 | 500 | 500 | 500 | 500 | ||
24 | 23 | BB | Nathan | T | * | * | * | * | * | * | * | * | ||
25 | 87 | BB | Janice | A | KL445877871 | 500 | 500 | 500 | 700 | 700 | 700 | 700 | ||
26 | 2 | BC | Mike | S | * | * | * | * | * | * | * | * | ||
27 | 3 | BA | Pike | A | KL445877873 | 600 | 600 | 600 | 600 | 600 | 600 | 600 | ||
Data |
I need to summarize as follows:
Excel Workbook | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | |||
3 | Unit | Rank | Name | AP_OL | AP_ON_RP | AP_ANN_GOLD | SP_OL_AP | IL_TUP_AP | RTUP_AP_NEW | SP_IL_AP | ||
4 | 2 | BA | Kite | * | * | * | * | * | * | * | ||
5 | 3 | BA | Pike | 1100 | 1000 | 1400 | 1400 | 1400 | 1300 | 1400 | ||
6 | 4 | BA | Mark | 1000 | 1500 | 1500 | 1300 | 1400 | 1200 | 1400 | ||
7 | 5 | BA | John | 1220 | 1300 | 1000 | 1300 | 1300 | 2100 | 1300 | ||
8 | 23 | BA | Debbie | * | * | * | * | * | * | * | ||
9 | 65 | BA | Joil | 1800 | 1600 | 2400 | 1500 | 1700 | 1900 | 1700 | ||
10 | 87 | BA | Moty | 2800 | 2000 | 1600 | 2200 | 2200 | 2000 | 2200 | ||
Summary |
The summary is based on the following criteria:
1) Sales manager ranked as BA are the group leaders and their down liners are BB and BC. Every sales manager are assigned a unit number and all sales under that unit number needs to be sum.
2) Only sales managers that their status is A which active needs to be summed. S stands for suspended and T stands for Terminated. Both S and T does not count as they are not active.
I tried to do this by pivot table but it is not able to sum in the way I wanted as per the above table. I need a formula solution. The reason is I have 5-6 reports to be prepared on daily basis and the rest of the reports I manage to formularize where I just copy and paste the raw data and the summary table populates automatically. For this report, I am currently using a combination of pivot table, vlookup and other manual methods.
Appreciate all the help on how I could formularize the table.