Sum total based on data from another worksheet

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Hello

I would like to sum the total hours in Sheet1 Column I based on data from Sheet2 where each shift is listed with the amount of hours. I managed to do this with a helper worksheet but I'm sure there's an easier and faster way.

Sheet1ABCDEFGHI
1MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
2Name1Warehouse
0600-1400
Warehouse
0600-1400
Warehouse
0600-1400
Extra
0900-1500
Warehouse
0600-1400
38
3Name2Warehouse
0600-1400
Warehouse
0600-1400
Warehouse
0600-1400
24
4Name3Reception
0800-1300
Reception
0800-1300
Reception
0800-1300
Reception
0800-1300
Reception
0800-1300
25
5Name4Reception
1300-1700
Reception
1300-1700
Reception
1300-1700
Reception
1300-1700
Reception
1300-1700
20

Sheet2AB
1Warehouse 0600-14008
2Warehouse 1400-22008
3Warehouse 2200-06008
4Extra 0900-15006
5Reception 0800-13005
6Reception 1300-17004
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
543
Office Version
365
Platform
Windows
Book1
ABCDEFGHIJKL
1MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
2Name1Warehouse 0600-1400Warehouse 0600-1400Warehouse 0600-1400Extra 0900-1500Warehouse 0600-14003838
3Name2Warehouse 0600-1400Warehouse 0600-1400Warehouse 0600-14002424
4Name3Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 0800-13002525
5Name4Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 1300-1700Reception 1300-17002023
6
7
8Sheet2AB
91Warehouse 0600-14008
102Warehouse 1400-22008
113Warehouse 2200-06008
124Extra 0900-15006
135Reception 0800-13005
146Reception 1300-17004
Sheet1
Cell Formulas
RangeFormula
K2:K5K2=SUM(IFERROR(VLOOKUP(MATCH(B2:H2,TRANSPOSE($B$9:$B$14),0),$A$9:$C$14,3,0),"FALSE"))
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
543
Office Version
365
Platform
Windows
Book1
ABCDEFGHIJK
1MONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAYSUNDAYTOTAL
2Name1Warehouse 0600-1400Warehouse 0600-1400Warehouse 0600-1400Extra 0900-1500Warehouse 0600-14003838
3Name2Warehouse 0600-1400Warehouse 0600-1400Warehouse 0600-14002424
4Name3Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 0800-13002525
5Name4Reception 0800-1300Reception 0800-1300Reception 0800-1300Reception 1300-1700Reception 1300-17002023
6
7
8AB
9Warehouse 0600-14008
10Warehouse 1400-22008
11Warehouse 2200-06008
12Extra 0900-15006
13Reception 0800-13005
14Reception 1300-17004
Sheet1
Cell Formulas
RangeFormula
K2:K5K2=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))


I forgot to edit Row No in Sheet 2
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
543
Office Version
365
Platform
Windows
Please press ctrl+shift+enter and not just enter after formula
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
[/RANGE]
Cell Formulas
RangeFormula
K2:K5K2=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))
I replaced "FALSE" with 0 and the formula seems to be working.

Would it be possible to sum the total based on the name in Column A without a helper column. Names will be listed multiple times. Example: EIGHT=8 & SIX=6. Example below
TOTAL
Name1EIGHT14
Name2SIXSIX12
Name1SIX14
Name3EIGHTEIGHT16
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
Cell Formulas
RangeFormula
K2:K5K2=SUM(IFERROR(INDEX($B$9:$B$14,MATCH(B2:H2,TRANSPOSE($A$9:$A$14),0)),FALSE))
Is it possible that this formula only works on an older version of excel, as this worked for me earlier but isn't working now for some reason on Office 2019?
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
56
It should work.
I've tried multiple times and for some reason it works on Office 2016, but only counts column B with Office 2019. I did press Ctrl+Shift+Enter when entering the formula.

Is there any chance the below can be done in one formula without a helper column?

Thanks again

Would it be possible to sum the total based on the name in Column A without a helper column. Names will be listed multiple times. Example: EIGHT=8 & SIX=6. Example below
TOTAL
Name1EIGHT14
Name2SIXSIX12
Name1SIX14
Name3EIGHTEIGHT16
 

Watch MrExcel Video

Forum statistics

Threads
1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top