EXCEL trying to count months

Anime King 20 Reputation points
2026-06-16T22:39:46.2+00:00

On Excel I am trying to make a cell that can count how many times a month shows up in a column while the month column is Date formatted.

ie: Month counter cell counts how many times January shows up in A3:A200 and those cells, A3:A200, are all formatted as "Date" not "General"

I have tried =COUNTIF, =COUNTIFS, =DATEDIF, =SUMPRODUCT. None work

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
Ashish Mathur 102.4K Reputation points Volunteer Moderator
2026-06-16T22:56:39.4133333+00:00

Hi,

In cell E2, enter this formula

=SUM(1*(TEXT(A2:A5,"mmm")=D2))

Hope this helps.

User's image

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-06-16T22:40:01.9966667+00:00

    Use a COUNTIF (or COUNTIFS) on the month extracted from the date values.

    Assuming the dates are in A3:A200 and the month to count is January:

    =COUNTIF(A3:A200, ">=" & DATE(2024,1,1))
  - COUNTIF(A3:A200, ">" & DATE(2024,1,31))

    This counts all dates in January 2024. Adjust the year as needed.

    To count all January dates regardless of year, use MONTH in a helper column, or use SUMPRODUCT, for example:

    =SUMPRODUCT(--(MONTH(A3:A200)=1))

    Here, 1 is January, 2 is February, etc. This works even when A3:A200 are true date values (Date format).

    For multiple conditions (for example, January dates between two limits), use COUNTIFS as described in the COUNTIFS documentation.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. IlirU 2,651 Reputation points Volunteer Moderator
    2026-06-17T05:15:16.9933333+00:00

    User's image

    Hi @Anime King

    In cell E2 apply below formula:

    =LET(
     date, A2:A200,
           SUM(--(IF(date = "", "", MONTH(date)) = MONTH(D2 & 1)))
)

    Below I’m giving you a shorter formula than the previous one, which uses the trimmed range option. Take a look at this formula: in the range A2:A200, after the colon there is also a dot. So the range is written as A2:.A200, and using the colon + dot (:.) makes Excel recognize the range only up to the last cell that contains data.

    =SUM(--(MONTH(A2:.A200) = MONTH(D2 & 1)))

    Be careful! If you change the range, you must always include colon + dot (:.) in the reference for the formula to work. For example, if you extend the range to row 300, then you need to write A2:.A300. Including the dot after the colon is essential for this formula to function correctly.

    I hope you’ll take a look at my solution and that it proves useful for addressing your issue.

    IlirU

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.