How to search (VLOOKUP?) by date and SUM it in different tabs

[Miro H]

Hello,

I've Excel with a lot of tabs which are tracking the working hours per day spent for various items.

I want to add all working hours per day in the main tab.

Here is very simplified example, I have a lot entries:

Tab1

Date	Hours
26.1.2019	20
27.1.2019	30
27.1.2019	5

<tbody>
</tbody>

Tab2

Date	Hours
27.1.2019	30
28.1.2019	40

<tbody>
</tbody>

Main tab

Date	Sum hours
26.1.2019	20
27.1.2019	65
28.1.2019	40

<tbody>
</tbody>

I've tried with VLOOKUP, put date as an argument and then manually add each tab.
=IFERROR(VLOOKUP(A1;'Tab1'!$A$1:$F$8;5;FALSE);0)+IFERROR(VLOOKUP(A1;'Tab2'!$A$1:$F$8;5;FALSE);0)+…….
IFERROR is for the case if all tabs do not have the same number of entries because blank field returns wrong values

Problems:

• If there are multiple entries for the same date in the tab only the value related to first date is added up. All the following entries with the same date are ignored
• I don't know how to dynamicly address different tabs so I don't have to add up manually each tab

What formulas should I use to achieve the good reslults?

 

[sandy666]

with PowerQuery:

Append Tab1 and Tab2 (you can do that with more tables with the same structure and format) then group result table by Date and Sum Hours

 

[ClaireS]

Place your cursor in the Main Tab, where you would like to place the top left corner of your results table. Then use Data, Data Tools, Consolidate. Select the ranges in Tab1, Tab2... then tick the boxes that say Headings in Top Row and Left Column, also Link to source data. This should give you a result sheet showing all the totals per day, in an outline view that expands to sow the individual items.