sumifs WITH eom DOESN'T WORK

[ChetanPuri]

Hello Excel Gurus,

Just need a favour wondering why my sumifs with EOM doesn't work. Any help would be greatly appreciated.

Book8
	A	B	C	D	E	F	G	H	I	J	K
1
2			Start Dat Sum Of values	31-Jan-19	28/02/2019	Mar
3				0	0	0
4
5
6
7
8		6-Jan-19	17-Jan-19	15-Feb-19	23-Jan-19	17-Feb-19	23-Mar-19	10-Jan-19	25-Feb-19
9		6555	4688	3275	2160	2404	3866	6649	5763
10
Sheet1

Cell Formulas
Range	Formula
D3:F3	D3	=SUMIFS($B$9:$I$9,$B$8:$I$8,">="&D2,$B$8:$I$8,"<="&EOMONTH(D2,0))

 

[Eric W]

Because your dates in D2 and E2 are already the end of the month. So in D3 it's adding anything between 1/31/19 and 1/31/19, which is nothing. Change the date in D2 to 1/1/2019 and you'll get a value. In F2, it's not even a date, just a text month.

 

[SunnyAlv]

the formula has no problem the problem is the date range from Jan 31st to the end of Jan is the same, so the SUMIFS formula doesn't sum any values

 

[Peter_SSs]

If you want to keep the row 2 headings as those end-of-month dates, you could alter the SUMIFS formula like this. I'm assuming that D3 should sum values for any date in Jan.

24 03 04.xlsm
	B	C	D	E	F	G	H	I
1
2			31-Jan-19	28-Feb-19	31-Mar-19
3			20052	11442	3866
4
5
6
7
8	06-Jan-19	17-Jan-19	15-Feb-19	23-Jan-19	17-Feb-19	23-Mar-19	10-Jan-19	25-Feb-19
9	6555	4688	3275	2160	2404	3866	6649	5763
SUMIFS

Cell Formulas
Range	Formula
D3:F3	D3	=SUMIFS($B9:$I9,$B$8:$I$8,">"&D$2-DAY(D$2),$B$8:$I$8,"<="&D$2)