Formula doesn't work in XLS but fine in XLSM file?

[CordingBags]

I have the following formula, provided by StephenCrump following an earlier question "Countifs Number Of Times Team Play on each weekday"
(many thanks) Stephen
=SUM((INDEX(D1:D198,2+3*INT((ROW(D1:D198)-ROW(D1))/3))=AC20)*(H1:N198=Y21))
this works real well if applied to the original XLSM file but not when used in "the same" XLS file.

All I have done different is to change the file save type.

Unfortunately I am forced to use XLS format for compatibility
I cannot see anything in the formula that is specifically XLSM and that shouldn't work in an XLS file. The XLS file just seems to ignore the formula and makes no change regardless of the data input.

Any help appreciated
Thanks

Paul

 

[GraH]

What if, you enter the formula using CTRL+SHIFT+ENTER?

 

[GraH]

Dismiss previous message, I was still editing but ran out of time.

Didn't the compatibility checker give you a clue when you saved the file?
Only thing I see in this formula, it's an array formula, that in legacy Excel needs to be confirmed using CTRL+SHIFT+ENTER. Normally, the conversion is build in. As shown below.
Or might it be the macro (since you have .xlsm) has turned of the auto calculation? Does clicking F9 forces a recalculation?

 

[Dave Patton]

edit with Excel 2016, try using SumProduct
The alternative does not require the Day column but the dates are shown for each relevant row.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	TEST			Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
2	Team	L4		4	0.7708333	Wed Oct 2, 24		L1	L3		L4	L2
3	Day	4				Wed Oct 2, 24		L7	L5		L8	L6
4	Count	3		Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
5				3	0.7708333	Tue Oct 15, 24				L7	L1	L3	L2
6	Team	L4				Tue Oct 15, 24				L8	L6	L4	L5
7	Day	4		Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
8	Count	3		5	0.7708333	Thu Oct 24, 24		L2			L1		L3	L7
9						Thu Oct 24, 24		L4			L5		L8	L6
10				Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
11				2	0.7708333	Mon Oct 28, 24		L7			L6	L1		L2
12						Mon Oct 28, 24		L5			L8	L4		L3
13				Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
14				6	0.7708333	Fri Nov 8, 24			L2	L1			L6	L7
15						Fri Nov 8, 24			L8	L3			L5	L4
16				Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
17				4	0.7708333	Wed Nov 13, 24		L5		L7	L2			L6
18						Wed Nov 13, 24		L8		L3	L1			L4
19				Day	Time	Date		R1	R2	R3	R4	R5	R6	R7
20				4	0.7708333	Wed Nov 20, 24		L7	L5	L6			L1
21						Wed Nov 20, 24		L2	L4	L3			L8
22
4c

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT((INDEX(D1:D21,2+3*INT((ROW(D1:D21)-ROW(D1))/3))=B3)*(H1:N21=B2))
B8	B8	=SUMPRODUCT((IFERROR(WEEKDAY(F1:F22)=B3,0)*(H1:N22=B2)))

 

[CordingBags]

Dismiss previous message, I was still editing but ran out of time.

Didn't the compatibility checker give you a clue when you saved the file?
Only thing I see in this formula, it's an array formula, that in legacy Excel needs to be confirmed using CTRL+SHIFT+ENTER. Normally, the conversion is build in. As shown below.
Or might it be the macro (since you have .xlsm) has turned of the auto calculation? Does clicking F9 forces a recalculation?
View attachment 110972

Thanks for the pointer, unfortunately I didn't / don't understand the formula well enough to appreciate the difference.
Suffice to say I will work with the XLSM file until final edit and then save it with the correctly calculated details as an XLS to upload.
The workbook now contains nearly 1600 entries spread over 16 tabs that would each presumably individually need to be changed
The XLSM works so path of least resistance
Thanks
Paul

 

[Dave Patton]

Did you try post # 4.
N.B.
• You can post the post to a clean sheet, Click on the icon below to f(x) in the heading, move to your sheet cell A1 and paste.
• Review the formulas with Formulas Review Formula
• For Excel 2016, the SumProduct formulas should work or you can edit the formulas that were suggested earlier and enter the formulas
with Ctrl+Shift+Enter not just enter.

 

[CordingBags]

Yes it was =SUMPRODUCT((INDEX(D1:D21,2+3*INT((ROW(D1:D21)-ROW(D1))/3))=B3)*(H1:N21=B2)) from post 4 that I applied to my workbook changing the data ranges where required that worked fine in XLSM, just not when I saved the file as XLS.
Though the results are stored which is all I need at that stage.
Thanks
Paul

 

[Dave Patton]

T202404.xlsm
	A	B	C			F	G	H	I	J	K	L	M	N
1								Number of Wins by Weekday Excel 2016
2	TEST					Date		R1	R2	R3	R4	R5	R6	R7
3	Team	L4				Wed Oct 2, 24		L1	L3		L4	L2
4	Day	4				Wed Oct 2, 24		L7	L5		L8	L6
5	Count	3				Date		R1	R2	R3	R4	R5	R6	R7
6	Count	3				Tue Oct 15, 24				L7	L1	L3	L2
7						Tue Oct 15, 24				L8	L6	L4	L5
8						Date		R1	R2	R3	R4	R5	R6	R7
9						Thu Oct 24, 24		L2			L1		L3	L7
10						Thu Oct 24, 24		L4			L5		L8	L6
11						Date		R1	R2	R3	R4	R5	R6	R7
12						Mon Oct 28, 24		L7			L6	L1		L2
13						Mon Oct 28, 24		L5			L8	L4		L3
14						Date		R1	R2	R3	R4	R5	R6	R7
15						Fri Nov 8, 24			L2	L1			L6	L7
16						Fri Nov 8, 24			L8	L3			L5	L4
17						Date		R1	R2	R3	R4	R5	R6	R7
18						Wed Nov 13, 24		L5		L7	L2			L6
19						Wed Nov 13, 24		L8		L3	L1			L4
20						Date		R1	R2	R3	R4	R5	R6	R7
21						Wed Nov 20, 24		L7	L5	L6			L1
22						Wed Nov 20, 24		L2	L4	L3			L8
23
4c

Cell Formulas
Range	Formula
B5	B5	=SUM((INDEX(D2:D22,2+3*INT((ROW(D2:D22)-ROW(D2))/3))=B4)*(H2:N22=B3))
B6	B6	=SUMPRODUCT((IFERROR(WEEKDAY(F2:F23)=B4,0)*(H2:N23=B3)))