Summarize Based on Multiple Criteria

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
Hi,

I have a data table as follows:

Excel Workbook
BCDEFGHIJKLM
3UnitRankNameStatusSales IDAP_OLAP_ON_RPAP_ANN_GOLDSP_OL_APIL_TUP_APRTUP_AP_NEWSP_IL_AP
45BAJohnAKL445877854220300300300300900300
54BAMarkAKL445877855100500500500500200500
665BDOmearaAKL445877856600400400400400500400
765BAJoilAKL445877856600400400400400500400
823BCElaineT********
923BADebbieT********
1087BBJaniceAKL445877848800500300300500400500
1187BAMotyAKL445877848800500300300500400500
122BCMikeS********
133BAPikeAKL445877850300200400300200500200
145BCNormanAKL445877851500500200500500700500
154BAMarkAKL445877821400500500400500600500
1665BCJulieAKL445877822200400900200400400400
1723BBNathanT********
1887BCNoraAKL445877824700500500900500500500
192BAKiteS********
203BBRanjitAKL445877847200200400500600200600
215BCNormanAKL445877848500500500500500500500
224BBVoightAKL445877849500500500400400400400
2365BDOmearaAKL445877850400400700500500500500
2423BBNathanT********
2587BBJaniceAKL445877871500500500700700700700
262BCMikeS********
273BAPikeAKL445877873600600600600600600600
Data


I need to summarize as follows:

Excel Workbook
BCDEFGHIJK
3UnitRankNameAP_OLAP_ON_RPAP_ANN_GOLDSP_OL_APIL_TUP_APRTUP_AP_NEWSP_IL_AP
42BAKite*******
53BAPike1100100014001400140013001400
64BAMark1000150015001300140012001400
75BAJohn1220130010001300130021001300
823BADebbie*******
965BAJoil1800160024001500170019001700
1087BAMoty2800200016002200220020002200
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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

kumara_faith

Well-known Member
Joined
Aug 19, 2006
Messages
618
Office Version
  1. 365
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,180
Messages
5,594,713
Members
413,924
Latest member
OMGSALESFORCE2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top