Sumifs across sheets

[bestephens]

On my worksheet (I am tracking 30 employees' training hours) I have duplicated their names unique identifiers on each worksheet and have linked each total to sheet one for a grand total. However, i am trying to replicate this in other regions' spreadsheets, and they track anywhere from over 150 - 390 people, and their list is all over the place.
I have copied the unique identifiers to a separate column, and when necessary, I use that column to add the results. Not every page needs this, since some pages are a simple sum across.
I need to link all of their totals on each worksheet and account for the worksheets that sum across.
My formula looks like this:
=SUM(MAT!AC3,JJA!AH3,IS!S3,OS!S2,'Emp Enrich'!AC3,'CA TOL'!Q3,'MH TOL'!Q3,'SA TOL'!Q3,'IDD TOL'!Q3,'Sups Training'!R4,'Sups Conf'!V3)

Since I need to include the unique ID in their formula, how can I do this?
Note: their MAT!AC3 is not correlated on each sheet, so it would need to find the matching ID and total and link it to the Grand Total.

My first inclination is to add another column of Unique IDs and total based on that.

If that is correct, could you show what that might look like?

Any help is greatly appreciated! Thank you!

[Ron Coderre]

Welcome to the forum.
Since this forum accommodates file attachments, I think your situation would be an excellent candidate. Can you post a sample workbook that is representative of your structure and data (without compromising confidential information, of course)?

[bestephens]

I will have to do this tomorrow, as I am leaving for the day. Please post which spreadsheet you would like me to post. Mine or one of the other regions? The other region workbook that I am working on is quite large!

Thank you for your help!

[Ron Coderre]

It would help if you could just post a shrinky-dink example of what you're working with. It doesn't have to contain *everything*...just enough for us to understand the kind of solution you want: a few sheets, a subset of the data, the results you want to see.

[bestephens]

It would help if you could just post a shrinky-dink example of what you're working with. It doesn't have to contain *everything*...just enough for us to understand the kind of solution you want: a few sheets, a subset of the data, the results you want to see.

I think this should do it.

On the MAT sheet (first one) I am trying to add the totals from the other worksheets in the Grand Total column. I have totals on each page, but the employee numbers do not match up on each page.

Thank you for all of your help!

[NBVC]

If you consistently placed your total column on each page, then you could use the method described here: Sum Across Multiple Sheets Based On Single Criteria

[bestephens]

If you consistently placed your total column on each page, then you could use the method described here: Sum Across Multiple Sheets Based On Single Criteria

Is there any other way? There are three spreadsheets similar to the big one, and this would be great for when we recreate the spreadsheet for next year, but for this year, that would be an incredible amount of work.

[NBVC]

How would you know then which columns have the grand totals in each sheet? You might as well do a separate SUMIF for each sheet and sum those all together, and copy down.

[bestephens]

How would you know then which columns have the grand totals in each sheet? You might as well do a separate SUMIF for each sheet and sum those all together, and copy down.

I do have a total on each page. And I am trying to populate that total w/ a grand total on the first sheet, however, if you look at the information, some people don't appear on every sheet. And some people appear multiple times on other sheets. I have used SUMIFS and/or SUMPRODUCTS on those pages. Now I am trying to get it to pull together in one lump sum. However w/out referencing the ID, my information will not be accurated.

Does that make sense?

[NBVC]

I mean like this:

In MAT!V3 of the Central Region workbook, enter formula:

Please Login or Register  to view this content.

copied down.