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: Newest
Most helpful Newest Oldest
  1. 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

  2. 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?

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.