Dear Excel Wizards

[HelpNeeded47]

I'm looking for a solution to a troubling formula. I'm trying to count entries from the past month, past 3 months and the past year. I know I'm going wrong somewhere as the latter two categories are coming back as 0s, when they should at least be returning the same total as the first.

="Closed this month:" &COUNTIFS(L5:L10020,"*Closed*",M5:M10020,"<="&EOMONTH(TODAY()-1,1)) &CHAR(10)&"Closed in past 3 months:" &COUNTIFS(L5:L10020,"*Closed*",M5:M10020,"<="&EOMONTH(TODAY(),-90)+1) &CHAR(10)&"Closed in past year:" &COUNTIFS(L5:L10020,"*Closed*",M5:M10020,"<=" & EOMONTH(TODAY(),-365)+2)

Is there anything glaringly obvious that needs to be changed? I'd also be very grateful if someone could explain to me what the number functions here actually do...

,"<="&EOMONTH(TODAY(),-90)+1)

 

[Joe4]

The second argument in the EOMONTH function is months, not days.
So you are telling it to go back 90 months!

See: EOMONTH function

 

[Fluff]

The 2nd argument in EOMONTH, is the number of months, so you are saying 90 months before today.

 

[HelpNeeded47]

The second argument in the EOMONTH function is months, not days.
So you are telling it to go back 90 months!

See: EOMONTH function

The 2nd argument in EOMONTH, is the number of months, so you are saying 90 months before today.

Thank you both, all fixed. A second or third pair of eyes can do wonders...

Mr K

 

[Joe4]

You are welcome.
Glad we could help!