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

Miro H

New Member
Joined
Mar 25, 2015
Messages
15
Office Version
  1. 365
Platform
  1. Windows
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,217,357
Messages
6,136,080
Members
449,989
Latest member
chrisgarcia78

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
Back
Top