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
934
Office Version
  1. 365
Platform
  1. Windows
How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
How did your query data set changed from the OP.

Anyway without helper column it can be calculated however you have to hard code the Criteria with value in the formula itself.

That was just an easier example as I would need the formula to work on 2000 rows.

Any help would be appreciated.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDE
1TOTAL
2Name1EIGHT14??? How did you get 14 here
3Name2SIXSIX12ok
4Name1SIX14??? How did you get 14 here
5Name3EIGHTEIGHT16
6
Sheet1


Please reply (What is your expected Result)
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57

ADVERTISEMENT

Book1
ABCDE
1TOTAL
2Name1EIGHT14??? How did you get 14 here
3Name2SIXSIX12ok
4Name1SIX14??? How did you get 14 here
5Name3EIGHTEIGHT16
6
Sheet1


Please reply (What is your expected Result)
Since names in column A row 2 & 4 match, then SUM B2:C2 + B4:C4. Is this possible without a helper column?
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
So i have entered only 2 Lookup values "Eight and Six, If you have more values than you can design a lookup table for automatic updation

Book1
ABCD
1TOTAL
2Name1EIGHT14
3Name2SIXSIX12
4Name1SIX14
5Name3EIGHTEIGHT16
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57

ADVERTISEMENT

Cell Formulas
RangeFormula
D2:D5D2=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))

Not sure why but my Excel 2016 is driving me crazy. When I tried the following formula, it resulted in D2 & D4 = 16, D3 & D5 = 8, which doesn't make any sense at all.

I then tried the exact same formula in google spreadsheets and it seems to work. Will try again tomorrow on Office 365 but i'm guessing it should work too. Thanks alot
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
With 2016 try confirming the formula in D2 with Ctrl Shift Enter, rather than just Enter & then drag down.
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Can you post the result using xl2bb

xl2bb is an excel addin available in the right hand side of reply box. Download and unzipp it and run. Then you will get an Mr Excel Addin.
Lets try why office 2016 is resulting in error.
Have you tried to confirm the formula with Ctrl+shift+Enter

Book1
ABCD
1TOTAL
2NAME1EIGHT16
3NAME2SIXSIX8
4NAME1SIX16
5NAME3EIGHTEIGHT8
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=SUMPRODUCT(($A$2:$A$5=A2)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

I entered the same formula in Office 2016 (with Ctrl+Shift+Enter) and google spreadsheets and got a different result for some reason so I'm not sure exactly what happened.
 

Forum statistics

Threads
1,136,769
Messages
5,677,627
Members
419,707
Latest member
Anna vib

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