Consolidated Report

Hi everyone,

So I have to run a report on all employees at my university every few weeks. In the automatically generated report I receive, the header column is the department name, the header row is the job type. The report breaks each department down so that each row represents the total number of a specific type of employee for that specific department, leaving 0's for the remaining cells in that row corresponding to the rest of the job types. This means that each department is listed multiple times, one for each type of employee working in that department. What I need is to consolidate the report so that each row tallies up all of the job types and puts them in one row for each department, and then groups departments in their respective building numbers. I attached a simplified version of this, the first sheet showing what I have to work with and the second sheet showing what I'm trying to achieve. The real report is typically 13,000-15,000 rows long and it changes week to week as people move in and out of positions across campus. I tried playing around with the Pivot Table options, but couldn't find a way to get it to display the way I wanted. I'm guessing there's got to be a simple way to do this but I can't figure it out. I would greatly appreciate any help with this! Thanks!

In C2 of "Consolidated Report"

=SUMIFS(INDEX('Original Report'!$C:$I,,MATCH(C$1,'Original Report'!$C$1:$I$1,0)),'Original Report'!$A:$A,$A2,'Original Report'!$B:$B,$B2)

Copy across to I and down to row 4

That's it! Thank you so much!

Now how would I collapse the first two columns down to just one department per line? (while still showing each department's building number)

Thanks again!

Not sure I follow: can you post sample of output required?

On the original report it lists each department (column b) multiple times and there may be multiple departments for each building. I need to consolidate the list so that each department is listed only once, with their corresponding building number as well. Here's another example of what I mean (attached). The first set of columns is how the original report looks. The second set of columns is how I want it to look in the end. On the first attachment I uploaded, I did this with just the three departments listed. I expanded this one to ten departments in five different buildings. (Also, I had to switch to a mac and I exported the file to an excel file so if it looks weird, that's why)

... which is what was provided: SUM by building and department.

=SUMIFS(INDEX('Original Report'!$C:$I,,MATCH(C$1,'Original Report'!$C$1:$I$1,0)),'Original Report'!$A:$A,$A2,'Original Report'!$B:$B,$B2)

A2=Building Number

B2=Department

Columns a and B of Consolidated Report just need buildings/departments listed as per your last post.