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
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
Try This Does it Work

Book1
ABCD
1Lookup
2NAME1EIGHT14
3NAME2SIXSIX12
4NAME1SIX14
5NAME3EIGHTEIGHT16
6
7
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,{"Eight","Six"},0),0),{1;2},{8;6}),0))
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Why not just change the CF rules to look at numbers instead?
At the end of the day numbers are numbers & should be be stored as such, it makes everything else so much easier.

Text will be the same as op and is needed. My only other option is to add a helper row with numbers under every row with data but this will double the size of my worksheet and slow down other formulas and CF.
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Is post 31 working????

Book1.xlsx
ABCD
1TOTAL
2NAME1EIGHT14
3NAME2SIXSIX12
4NAME1SIX14
5NAME3EIGHTEIGHT16
6
7EIGHT8
8SIX6
Sheet2
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(LOOKUP(($A$2:$A$5=A2)*IFERROR(MATCH($B$2:$C$5,$A$7:$A$8,0),0),{1;2},$B$7:$B$8),0))
Press CTRL+SHIFT+ENTER to enter array formulas.


Yes, it's working. Can you confirm that I'm linking the lookup table correctly?

Thanks again to both for all your help.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This formula will not result in correct answer as look up function is based on Approximate match. See this example

Book1
DEFGH
14Two9Two2
15Four4
16Three3
17Five5
18One1
19Six6
20Eight8
21Seven7
22Nine9
Sheet1
Cell Formulas
RangeFormula
E14E14=LOOKUP(D14,G14:G22,H14:H22)


It is returning 9 for TWO
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
However i missed the {1;2} this make the formula error free.
Anyway please ensure that $A$7:$A$8 and {1;2} are related.

See this

Book1
ABCD
1TOTAL
2NAME1EIGHT21
3NAME2SIXone7
4NAME1SIX21
5NAME3EIGHTEIGHT16
6Name1FourThree21
7Name 5TwoNine11
8Name6ThreeSeven10
9
10
11
12
13EIGHT8
14SIX6
15One1
16Five5
17Two2
18Three3
19Nine9
20Four4
21Seven7
22
Sheet3
Cell Formulas
RangeFormula
D2:D8D2=SUM(IFERROR(LOOKUP(($A$2:$A$8=A2)*IFERROR(MATCH($B$2:$C$8,$A$13:$A$21,0),0),{1;2;3;4;5;6;7;8;9},$B$13:$B$21),0))
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Book1
ABCDE
1HELPERTOTAL
2NAME1EIGHT814
3NAME2SIXSIX1212
4NAME1SIX614
5NAME3EIGHTEIGHT1616
6
7EIGHT8
8SIX6
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUM(IFERROR(INDEX($B$7:$B$8,N(IF(1,MATCH(B2:C2,$A$7:$A$8,0)))),0))
E2:E5E2=SUMIF($A$2:$A$5,A2,$D$2:$D$5)
Press CTRL+SHIFT+ENTER to enter array formulas.


I ended up using the formula above with a helper column as it seems faster than Vlookup/Lookup formulas. Is there an easy way to join these two formulas together to eliminate the helper column?

Thanks to both for your help.
 

Forum statistics

Threads
1,141,302
Messages
5,705,587
Members
421,400
Latest member
chakam

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