EXCEL SUMIFS

[kelvin_9]

greeting to all,

wondering what is the problem on my fromula on sheet_summary ?

thank you very much for your guidence

NEW.xlsx
	A	B	C			F	G	H	I	J	K	L
14	Sunday, 2 October 2022					100					100
15	Monday, 3 October 2022
16	Tuesday, 4 October 2022
17	Wednesday, 5 October 2022
18	Thursday, 6 October 2022
FY23 - 02OCT2022 ~ 30SEP2023

Cell Formulas
Range	Formula
K14	K14	=SUM(F14:J14)
A15:A18	A15	=A14+1

NEW.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	SEPTEMBER	OCTOBER	NOVEMBER	DECEMBER	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER	OCTOBER	TOTAL
3	0	0	0	0	0	0	0	0	0	0	0	0	0	0
SUMMARY

Cell Formulas
Range	Formula
D3:Q3	D3	=SUMIFS('FY23 - 02OCT2022 ~ 30SEP2023'!$F:$F,'FY23 - 02OCT2022 ~ 30SEP2023'!$A:$A,">="&SUMMARY!D$2,'FY23 - 02OCT2022 ~ 30SEP2023'!$A:$A,"<="&EOMONTH(SUMMARY!D$2,0))

 

[Alex Blakenburg]

Your headings on the Summary sheet need to be actual dates for that to work.

 

[kelvin_9]

Your headings on the Summary sheet need to be actual dates for that to work.

thank you for your reply Alex Blakenburg

maybe not?
i can use the same formula on old workbook
D3 is fine, but D40 is not wokring well, so i try to create a new workbook

131 - STWC RECORD 1.1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
2	OCTOBER	NOVEMBER	DECEMBER	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER
3	819	300	801	1929	1709	3258	5830	6313	6320	8919	11176	243
SUMMARY

Cell Formulas
Range	Formula
D3:O3	D3	=SUMIFS('FY23 - 02OCT2022 ~ 30SEP2022'!$F:$F,'FY23 - 02OCT2022 ~ 30SEP2022'!$A:$A,">="&SUMMARY!D$2,'FY23 - 02OCT2022 ~ 30SEP2022'!$A:$A,"<="&EOMONTH(SUMMARY!D$2,0))

131 - STWC RECORD 1.1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
39	OCTOBER	NOVEMBER	DECEMBER	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER
40	0	0	0	0	0	0	0	0	0	0	0	0
SUMMARY

Cell Formulas
Range	Formula
D40:O40	D40	=SUMIFS('FY24'!$F:$F,'FY24'!$A:$A,">="&SUMMARY!D$2,'FY24'!$A:$A,"<="&EOMONTH(SUMMARY!D$2,0))

 

[Fluff]

The values in row 2 are dates that have been formatted to show just the month, whereas the values in row 39 are text.

 

[kelvin_9]

The values in row 2 are dates that have been formatted to show just the month, whereas the values in row 39 are text.

thank you very much for your reply, Fluff

how should i amend it?
even though i copy row 2 & replace to row 39
with range at D39, still dosent work

131 - STWC RECORD 1.1.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O
39	OCTOBER	NOVEMBER	DECEMBER	JANUARY	FEBRUARY	MARCH	APRIL	MAY	JUNE	JULY	AUGUST	SEPTEMBER
40	0	0	0	0	0	0	0	0	0	0	0	0
SUMMARY

Cell Formulas
Range	Formula
D40:O40	D40	=SUMIFS('FY24'!$F:$F,'FY24'!$A:$A,">="&SUMMARY!D$39,'FY24'!$A:$A,"<="&EOMONTH(SUMMARY!D$39,0))

 

[Fluff]

Without knowing what is on the FY24 sheet it's difficult to say, but I suspect that the values in col A are not real dates.

 

[Alex Blakenburg]

You seem to have changed your FY24 sheet name but your formula is working fine for me.
To test Fluff's theory and check whether they are real dates change the date format. If nothing changes the values are text. If they change they are being recognised as dates.

Book9
	D	E	F	G	H	I	J	K	L	M	N	O
39	October	November	December	January	February	March	April	May	June	July	August	September
40	100	300	0	0	0	0	0	0	0	0	0	0
Summary

Cell Formulas
Range	Formula
D40:O40	D40	=SUMIFS('FY24'!$F:$F, 'FY24'!$A:$A, ">="&D$39, 'FY24'!$A:$A, "<="&EOMONTH(D$39,0))

Book9
	A	B	C	D	E	F
14	Sunday, 2 October 2022					100
15	Monday, 3 October 2022
16	Tuesday, 4 October 2022
17	Wednesday, 5 October 2022
18	Thursday, 6 October 2022
19	Tuesday, 15 November 2022					200
20	Friday, 25 November 2022					100
FY24

Cell Formulas
Range	Formula
A15:A18	A15	=A14+1

 

[kelvin_9]

thank you very much for your reply, Fluff & Alex Blakenburg

i have found the reason and amened successfully with your advise

thank you very much

 

[Fluff]

Glad it's sorted & thanks for letting us know.