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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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
138
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,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

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