Best way to add up multiple department worksheets by account number and month

[carbus73]

I am building our 2018 corporate budget and have 1 summary worksheet and 9 department worksheets that have the account number in the first column and January through December across the top row. I want to add up all of the department expenses in the summary worksheet for each month by account number, but Iam hesitant to do 3D sum in case one of the rows is missing in a department or gets added later. What is the best way to add up the department expenses that ensures I am adding the correct month and account for each department.( I am thinking Index, match or something but I am not that familiar). Any advice would be GREATLY appreciated!

 

[Asman_Kei]

It might be easiest to first check if all the sheets have the same first column. If so, you can do the 3d sum and save yourself major headache. To check if the first columns are all the same, I would create a separate sheet and paste all the first columns of the individuals sheets into it (Column A would have the first column of sheet 1, column b would have the first column of sheet 2, etc). in the 10th column, (Column J) I would have a formula that determines if the preceding 9 values are the same

=A1=B1=C1=D1=E1=F1=G1=H1=I1

I would fill this formula down to the last row. If there is no FALSE value in Column J (Do a filter to see), you can go ahead with a 3D sum. If there are any FALSE values, you may be able to resolve them easily by inserting rows where necessary in the sheets that are producing the FALSE value, and then proceed with a 3D Sum. If you can't easily make all of column J turn TRUE, then VLOOKUP or index/match might be the solution, but I think whatever solution you would get out of that would be slower than a 3d Sum.

 

[carbus73]

Thank you! It is very helfpul to get confirmation that 3D sum is the best approach here - I wanted to make sure I wasn't missing something obvious.