Subtracting hours in a work schedule

If you're talking about cell D4, that make no sense based on the changes so far. D4 clearly is the hours in B4:C4. You have the same column on sheet2 to cover the other store, and you can sum the total hours of the two sheets off to the right, where it makes sense to do so.


If you really expect D4 to reflect both sheets (which will read very oddly since you will only be able to see one sheet, but see a total for two...very odd)...then you will have to create the same formula twice in that cell: Once as is.. then + the same formula again, this time time "Store 15" sheet references added to everything:

=IF(B4="", 0, IF(COUNTA(B4:C4)=2, (C4-B4) * 24 - 0.5, IF(ISNUMBER(MATCH(B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH(B4,'Lists for Stores'!$C:$C,)), 0))) + IF('Store 15'!B4="", 0, IF(COUNTA('Store 15'!B4:C4)=2, ('Store 15'!C4-'Store 15'!B4) * 24 - 0.5, IF(ISNUMBER(MATCH('Store 15'!B4, 'Lists for Stores'!$C:$C,)), INDEX('Lists for Stores'!$D:$D, MATCH('Store 15'!B4,'Lists for Stores'!$C:$C,)), 0)))