Summing up Unique Value across multiple worksheets

[rmrossetti]

Hi All!

I need to sum up a person's allocations across multiple worksheets, 6 in total. Any Suggestions? For instance, How many hours is Jane Doe Allocated across each book.

 

[CyrusTheVirus]

Can you post a sample of your data showing how things are setup in say 3 worksheets and show what you are trying to sum and exactly how you want it to look in the end as well?

 

[rmrossetti]

Here is an example of the data that is uniform across multiple books labeled "Team 1", "Team 2", "Team 3", etc.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Dept[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]OPEN AMP[/TD]
[TD]Production[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]OPEN PM[/TD]
[TD]Production[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


It would need to sum up on a separate tab Labeled "Allocations"
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Position[/TD]
[TD]Dept[/TD]
[TD]Allocated Amount[/TD]
[/TR]
[TR]
[TD]OPEN AMP[/TD]
[TD]Production[/TD]
[TD]Need Formula[/TD]
[/TR]
[TR]
[TD]OPEN PM[/TD]
[TD]Production[/TD]
[TD]Need Formula[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

How many worksheets will you be using that have a setup like the below? And also you mention that you want to know how many hours Jane Doe has, but I don't see her name mentioned, I only see position and department...?

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Dept[/TD]
[TD]Allocation[/TD]
[/TR]
[TR]
[TD]OPEN AMP[/TD]
[TD]Production[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]OPEN PM[/TD]
[TD]Production[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[rmrossetti]

Sorry, instead of Jane Doe just use the position, that is the unique value I need to sum up. The allocation is the hours amount. It will be 8 worksheets that the data is spread across. All with the same set up. Only difference will be 50-100 people each worksheet, and could have that same person across multiple worksheets.

 

[Aladin Akyurek]

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$A$2:$A$7&"'!C:C"),INDIRECT("'"&$A$2:$A$7&"'!A:A"),$A2,INDIRECT("'"&$A$2:$A$7&"'!B:B"),$B2))

where A2:A7 houses the relevant sheet names, column C the allocation values to sum, column A positions that must satisfy a position given in A2, and column B departments that must satisfy a department value in B2.

 

[rmrossetti]

Awesome I'll give it a shot!

What is the purpose of the "'"

 

[CyrusTheVirus]

Hi Aladin,

Why do #VALUE! errors appears when evaluating this formula, yet it works?

 

[Aladin Akyurek]

Awesome I'll give it a shot!

What is the purpose of the "'"

No need for it if the relevant sheet name consist of a single word like Sheet1. When we have a sheet name like:

January 2016

which contains a space, we must put a pair of single quotes around the name, that is,

'January 2016'

Hence: "'".

 

[Aladin Akyurek]

Hi Aladin,

Why do #VALUE! errors appears when evaluating this formula, yet it works?

Try to restrict references to a small range instead of whole columns. Do the evaluation by bits of the formula using F9.