Subtracting hours in a work schedule

To add in the hours for split shifts you've entered as SPLIT1 and SPLIT2, put this formula in D4 and copy down:
=IF(COUNTA(B4:C4)=2,(C4-B4)*24-0.5,IF(B4="SPLIT1",9,IF(B4="SPLIT2",7.25,0)))

UPDATE: Since you appear to have decided NOT to have one group of names collect all the hours from all the sections on both sheet, the formulas needed in AB and AC can simplify down.

AB:
=SUMPRODUCT(($B4:$R4<0.5) * ($B4:$R4>0) * (MOD(COLUMN($B3:$R3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$B4:$R4<0.5) * ('Store 15'!$B4:$R4>0) * (MOD(COLUMN('Store 15'!$B3:$R3)-1,3)=1))

AC:
=SUMPRODUCT(($A$4:$A$13=$Z4) * ($B$4:$R$13>=0.5) * (MOD(COLUMN($B$3:$R$3)-1,3)=1)) + SUMPRODUCT(('Store 15'!$A$4:$A$13=$Z4) * ('Store 15'!$B$4:$R$13>=0.5) * (MOD(COLUMN('Store 15'!$B$3:$R$3)-1,3)=1))

NOTE: The two formulas above do not require you to press CTRL-SHIFT-ENTER to confirm them. I noticed you had array-entered the original formulas and want to make sure you know that is not needed.

AD3 - SPLIT1
AD4:
=COUNTIF($B4:$R4,AD$3)

AE3 - SPLIT2
AE4:
=COUNTIF($B4:$R4,AE$3)