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
Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Select the D2 to D5 and Press F2 and Then Press Control+Shift+Enter

I tried this and D2 to D5 all equal 14. This is correct for D2, but then D3 to D5 are also referencing ($A$2:$A$5=A2) as they all have the same formula. If I try to edit the formula or insert a row or column, the following error displays: You can't change part of an array.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
If you select D3 & use the evaluate button on the formula tab do you see this as the last step
1595788704862.png
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
If you select D3 & use the evaluate button on the formula tab do you see this as the last step
View attachment 19032

All steps are listed below:

SUMPRODUCT(($A$2:$A$5=A3)*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({"NAME1";"NAME2";"NAME1";"NAME3"}="NAME2")*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(VLOOKUP("EIGHT",{"Eight",8;"Six",6},2,0),0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*IFERROR(8,0))
SUMPRODUCT(({FALSE;TRUE;FALSE;FALSE})*8)
SUMPRODUCT(({0;8;0;0})
8
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case it appears that the formula only works with dynamic aware Excel. Which is 365 with the dynamic array functions.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
934
Office Version
  1. 365
Platform
  1. Windows
Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57

ADVERTISEMENT

Try only this in VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0) in E2 and Drag down till F5 and post the result using xl2bb
Book1.xlsx
ABCDE
1TOTAL
2NAME1EIGHT168
3NAME2SIXSIX88
4NAME1SIX168
5NAME3EIGHTEIGHT88
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))
E2:E5E2=VLOOKUP($B$2:$C$5,{"Eight",8;"Six",6},2,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

Is there a workaround for this without dynamic array formulas as I would prefer if this would be compatible with older excel versions too.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
Why not just put the numbers into the cells, rather than the words?
 

Mux99

Board Regular
Joined
Apr 15, 2019
Messages
57
Why not just put the numbers into the cells, rather than the words?

Conditional formatting is being used depending on the text so I can't just use numbers. All matching text need to be linked to a number, and I need the total sum based on the name in Column A as they are listed multiple times.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,131
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,770
Messages
5,677,630
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