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.

A

B

C

<tbody>
</tbody>

Position	Dept	Allocation
OPEN AMP	Production	1
OPEN PM	Production	1

<tbody>
</tbody>

It would need to sum up on a separate tab Labeled "Allocations"

A	B	C
Position	Dept	Allocated Amount
OPEN AMP	Production	Need Formula
OPEN PM	Production	Need Formula

<tbody>
</tbody>

 

[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...?

Position	Dept	Allocation
OPEN AMP	Production	1
OPEN PM	Production	1

<tbody>
</tbody>

 

[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.