Summarize Based on Multiple Criteria

[kumara_faith]

Hi,

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.

 

[kumara_faith]

Hi,

Just to add that the columns can change, meaning the column headings might not always be in the same column. Example, Unit sometimes can be in column A and the next report, the data from IT might show the Unit in column B.

 

[kumara_faith]

Hi,

I have a cross post here:

Summarize Based on Multiple Criteria

 

[kumara_faith]

bump.