Summarize Based on Multiple Criteria

[Kumara_faith]

Hi ,

I have a data table as per the attachment in sheet Data. I need to summarize the data as per the table in the sheet Summary 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 to the group leader.
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.
3) The column headings in the data table 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.The formula needs to be able to look through the column headings and sum based on the criteria above.

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 and this takes considerable time as the actual raw data has about 10,000 rows of data.

Appreciate all the help on how I could formularize the table.

I have a cross post here:

http://www.mrexcel.com/forum/excel-q...ml#post4198079

[Glenn Kennedy]

Use this in E14, drag across & down

=SUMIFS(Data!G:G,Data!$B:$B,$B14)

[Kumara_faith]

Hi Kennedy,

Thanks you for your response.

1) Is there a way to populate the unit number is ascending order without the duplicates in the summary table and only for units which are active ?
2) Can the above formula work even if the unit column changes - sometimes the data from IT can place the unit column in column A and sometimes in column B in the raw data.