Lookup and Sum values across multiple sheets

[xblft1]

Hi all,

I have performance data for multiple employees spread across multiple sheets (one sheet for each day of the month).
I am trying to get monthly totals for each column.
The employee name is not always in the same row in all sheets and might not be present at all.

Example of what each sheet of my data looks like:

Apples	Oranges	Cherries
Employee 1
Employee 2

<tbody>
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]

[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]Berries[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]Employee 3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

I am able to get the sum of apples per each with VLOOKUP()+VLOOKUP()+.....
Is there a more concise way to get to this result, that will also be flexible to adding new sheets?

many thanks in advance for looking.

 

[DRSteele]

Pivot Tables were invented to make quick work of this type of task.

 

[xblft1]

Hey DRSteele, thank you for taking the time to read & respond.
I've only used Pivot tables for data on one sheet - not sure how it would work for what I'm trying to achieve, where data is spread across multiple sheets and needs to be found and added.
I'm possibly missing the obvious, if you have the chance to elaborate or point me to some example I'd greatly appreciate it.

 

[DRSteele]

Well let's see. The data can certainly reside on any sheet. It's best to collect data in official Excel Tables; that way referring to the data is simpler.

I suppose watching some videos on youtube might illuminate things. Try this excellent video from ExcelIsFun: https://www.youtube.com/watch?v=e-yuYNgsHAk