Just like the title says in Row 1 i have a simple sum formula that works in some cases but in others it doesnt sum the numbers. The following snip shows 9 when the sum should be 40?
SRI Schedule Prototype.xlsx | |||||||
---|---|---|---|---|---|---|---|
JH | JI | JJ | JK | JL | |||
1 | 0 | 9 | 9 | 9 | 9 | ||
2 | Dec | ||||||
3 | 26 | 27 | 28 | 29 | 30 | ||
4 | |||||||
5 | 8 | 8 | 8 | 8 | |||
6 | |||||||
7 | |||||||
8 | |||||||
9 | 8 | 8 | 8 | 8 | |||
10 | |||||||
11 | |||||||
12 | 8 | 8 | 8 | 8 | |||
13 | |||||||
14 | |||||||
15 | |||||||
16 | 8 | 8 | 8 | 8 | |||
17 | |||||||
18 | |||||||
19 | |||||||
20 | 8 | 8 | 8 | 8 | |||
21 | |||||||
22 | |||||||
23 | |||||||
24 | |||||||
25 | |||||||
26 | |||||||
27 | |||||||
Schedule |
Cell Formulas | ||
---|---|---|
Range | Formula | |
JH1:JL1 | JH1 | =SUM(JH4:JH27) |
JH2 | JH2 | =CHOOSE(MONTH(JJ3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec") |
JH3:JL3 | JH3 | =WORKDAY(JG3,1) |
JH4:JL4 | JH4 | =IF($G4="","",IF(AND(JH$3>=$F4,NOT(COUNTIFS(Holidays!$F$3:$F$34,JH$3)),JH$3<=$G4),8,"")) |
JH5:JL27 | JH5 | =IF($G5="","",IF(AND(JH$3>=$F5,NOT(COUNTIFS(Holidays!$F$3:$F$34,JH$3)),JH$3<=$G5-1),8,"")) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
M3:JQ27,JQ4:NR27,M5:NR27 | Expression | =COUNTIFS(Holidays!$F$3:$F$34,M$3) | text | NO |
4:50 | Expression | =AND(NOT(ISBLANK($B4)),ISBLANK($A4)) | text | NO |