Forum Discussion

Copper Contributor

Nov 10, 2022

COUNTIFS by Month Excluding Year

I'm attempting to do a COUNTIFS function based on a date range but want to exclude the year so that the date can be updated easily from one year to the next without having to redo all of the formulas...

excel

Formulas and Functions

Patrick2788

Silver Contributor

Nov 10, 2022

busymamabee

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:

busymamabee
Copper Contributor
Nov 11, 2022
Patrick2788

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?
- Patrick2788
  Silver Contributor
  Nov 11, 2022
  busymamabee
  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)))
  - busymamabee
    Copper Contributor
    Nov 12, 2022
    Patrick2788
    
    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?