Why Does this sum formula not work properly?

[keef2]

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

 

[keef2]

Worth noting is this formula seems to work if in the bottom row? However, i want it in row 1.

SRI Schedule Prototype.xlsx
	JH	JI	JJ	JK	JL	JM	JN	JO	JP	JQ
1	0	9	9	9	9	0	9	1	1	16
2	Dec					Jan
3	26	27	28	29	30	2	3	4	5	6
4
5		8	8	8	8		8	8	8
6
7
8
9		8	8	8	8		8	8	8
10
11
12		8	8	8	8		8	8	8	8
13
14
15
16		8	8	8	8		8	8	8	8
17
18
19
20		8	8	8	8		8
21
22
23
24
25
26
27
28	0	40	40	40	40	0	40	32	32	16
Schedule

Cell Formulas
Range	Formula
JH1:JQ1	JH1	=SUM(JH4:JH27)
JH2,JM2	JH2	=CHOOSE(MONTH(JJ3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
JH3:JQ3	JH3	=WORKDAY(JG3,1)
JH4:JQ4	JH4	=IF($G4="","",IF(AND(JH$3>=$F4,NOT(COUNTIFS(Holidays!$F$3:$F$34,JH$3)),JH$3<=$G4),8,""))
JH5:JQ27	JH5	=IF($G5="","",IF(AND(JH$3>=$F5,NOT(COUNTIFS(Holidays!$F$3:$F$34,JH$3)),JH$3<=$G5-1),8,""))
JH28:JQ28	JH28	=SUM(JH4:JH27)

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

 

[keef2]

Ok no idea what happend here but its working now? I just cut and pasted the last row to row 1 and copied it over? Must have been something with formatting? Any explanation?

SRI Schedule Prototype.xlsx
	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG
1	40	40	40	48	48	48	48	48	48	40
2	Dec					Dec
3	12	13	14	15	16	19	20	21	22	23
4
5	8	8	8	8	8	8	8	8	8	8
6
7
8
9	8	8	8	8	8	8	8	8	8	8
10
11
12	8	8	8	8	8	8	8	8	8	8
13
14
15
16				8	8	8	8	8	8	8
17
18
19
20	8	8	8	8	8	8	8	8	8	8
21
22
23
24
25
26	8	8	8	8	8	8	8	8	8
27
28
Schedule

Cell Formulas
Range	Formula
IX1:JG1	IX1	=IF(SUM(IX4:IX27)=0,"",SUM(IX4:IX27))
IX2,JC2	IX2	=CHOOSE(MONTH(IZ3),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
IX3:JG3	IX3	=WORKDAY(IW3,1)
IX4:JG4	IX4	=IF($G4="","",IF(AND(IX$3>=$F4,NOT(COUNTIFS(Holidays!$F$3:$F$34,IX$3)),IX$3<=$G4),8,""))
IX5:JG27	IX5	=IF($G5="","",IF(AND(IX$3>=$F5,NOT(COUNTIFS(Holidays!$F$3:$F$34,IX$3)),IX$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:27,29:50,A28:JB28,JV28:XFD28	Expression	=AND(NOT(ISBLANK($B4)),ISBLANK($A4))	text	NO
M1:NR1	Expression	=AND(NOT(ISBLANK($B28)),ISBLANK($A28))	text	NO

 

[Peter_SSs]

Any explanation?

Calculation was set to Manual?

 

[smozgur]

The only logical explanation is the calculation was set to manual in this question. Therefore, I am marking the corresponding answer as the solution.