Forum Discussion
COUNTIFS by Month Excluding Year
Presuming dates are in A2:A10. You could try:
=COUNT(IF(MONTH(A2:A10)=1,A2:A10))Press ctrl+shift+enter to define as an array if not using 365 or Excel 2021.
The reason COUNTIF/COUNTIFS does not work is both functions only accept ranges, not arrays. That's why this one won't calculate:
I think this is on the right path, but it's coming back with a crazy number as the result. If it helps, the dates listed are on a separate tab in a named column F. The result should be 0 based on the dates I have listed currently, but it's coming up as 1048573.
What am I missing?
It appears you have lots of 0s in that column. Try this and make sure the formula is not pointed at the entire column:
=COUNT(IF(TEXT(A2:A10,"mmm")="Jan",IF(A2:A10<>0,A2:A10)))The 0s in column E are a sum equation for C - D to determine the remaining Goal. The column I'm pulling from is on a separate tab. The goal is for the equation on the Goals sheet in Column D to count the number of closed dates in the month shown in Column A, pulling it from the named column F on the second tab. Does that make sense?
You could use of these two options:
Legacy ctrl+shift+enter array
=COUNT(IF(TEXT(Sheet2!$F$2:$F$10,"mmmm")=A2,IF(Sheet2!$F$2:$F$10<>0,Sheet2!$F$2:$F$10)))365:
=LET(dates,Sheet2!$F$2:$F$10,filtered,FILTER(dates,(TEXT(dates,"mmmm")=A2)*(dates<>0)),COUNT(filtered))