Sum total based on data from another worksheet

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. 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
934
Office Version
  1. 365
Platform
  1. 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
934
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Please press ctrl+shift+enter and not just enter after formula
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57

ADVERTISEMENT

[/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
57
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
57
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
 

Forum statistics

Threads
1,137,155
Messages
5,679,921
Members
419,862
Latest member
Bluewings666

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