# Sum total based on data from another worksheet

#### Mux99

##### Board Regular
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.

 Sheet1 A B C D E F G H I 1 MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY TOTAL 2 Name1 Warehouse 0600-1400 Warehouse 0600-1400 Warehouse 0600-1400 Extra 0900-1500 Warehouse 0600-1400 38 3 Name2 Warehouse 0600-1400 Warehouse 0600-1400 Warehouse 0600-1400 24 4 Name3 Reception 0800-1300 Reception 0800-1300 Reception 0800-1300 Reception 0800-1300 Reception 0800-1300 25 5 Name4 Reception 1300-1700 Reception 1300-1700 Reception 1300-1700 Reception 1300-1700 Reception 1300-1700 20

 Sheet2 A B 1 Warehouse 0600-1400 8 2 Warehouse 1400-2200 8 3 Warehouse 2200-0600 8 4 Extra 0900-1500 6 5 Reception 0800-1300 5 6 Reception 1300-1700 4

#### CA_Punit

##### Well-known Member
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### Mux99

##### Board Regular
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.

#### CA_Punit

##### Well-known Member
Is post 31 working????

#### Mux99

##### Board Regular
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

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
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
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.

Replies
3
Views
49
Replies
6
Views
189
Replies
8
Views
194
Replies
2
Views
155
Replies
11
Views
308

1,141,299
Messages
5,705,573
Members
421,399
Latest member
hjweiss00

### 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.

### Which adblocker are you using?

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

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