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

Miro H

New Member
Joined
Mar 25, 2015
Messages
12
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:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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
152
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,659
Messages
5,838,643
Members
430,558
Latest member
Krampus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top