EOMONTH Based on Month Doesn't Consider some dates

[Paiwand]

Dear,

i have used this:

=AVERAGEIFS($N$4:$N$981;$J$4:$J$981;">="&(5&$AG8)+0;$J$4:$J$981;"<="&EOMONTH(5&$AG8;0))

for getting average of specific column while beside contains specific month.

N Contains Number
J Contains Dates
AG8 Contains May

but this formula seems doesn't consider that dates which is its day under 12, for example:

10/May/2018 10
20/May/2018 20
23/May/2018 30

currently it's return 25, while it should be return 20, because it doesn't consider first one because the Day is under 12.

please your help.

Thanks.

 

[Dave Patton]

consider
=AVERAGEIFS($N$4:$N$981,$J$4:$J$981,">="&(12&$AG8)+0,$J$4:$J$981,"<="&EOMONTH(5&$AG8,0))

N.B. Your formula considers 5-May-18. My edit shows 12-May-18.

 

[Paiwand]

dear Dave,

what's the deference ? actual it doesn't work sorry, please i am still awaiting for proper feedback.

 

[Dave Patton]

Ensure you replace the commas to what your region requires.

Excel 2010
	G	H	I	J	K	L	M	N
4	Your formula	20		10-May-18				10
5	Edited formula	25		20-May-18				20
6				23-May-18				30
3a

Cell Formulas
Range	Formula
H4	=AVERAGEIFS($N$4:$N$981,$J$4:$J$981,">="&(5&$AG8)+0,$J$4:$J$981,"<="&EOMONTH(5&$AG8,0))
H5	=AVERAGEIFS($N$4:$N$981,$J$4:$J$981,">="&(12&$AG8)+0,$J$4:$J$981,"<="&EOMONTH(5&$AG8,0))