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

Miro H

New Member
Joined
Mar 25, 2015
Messages
6
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?
 
Last edited:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
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
 
Last edited:

ClaireS

Board Regular
Joined
Jul 29, 2013
Messages
137
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top