# Count Dates Per Month in a Range of random dates.

Copper Contributor

# Count Dates Per Month in a Range of random dates.

I have a range of dates listed below.

I want to count the entire list and sometimes this list gets large.
for example, the latest list size was from B8 to B612.

I would like to have a formula that lists for example how many dates are in each month.

I thought i could use =SUMPRODUCT(--(MONTH(B8:B612)=12))

This should scan the entire range of B8 to B612 and report how many dates were in Dec.

However, it gives the #VALUE! If you look at it further it shows "Error in Value"

If I keep the formula the same but reduce the list to just B8:B48, it works fine and reports 41 back.
for example =SUMPRODUCT(1*(MONTH(B8:B48)=12))

I am not sure why the range is causing the problem. I checked to make sure that each DATE was correct.  No spaces for example.

I even tried changing the Date Type.  Got the same problem any way the data was formulated.

Are there any other ways to count how many date entries there are for each month in a given range?

Thanks

B

12/30/2021
12/30/2021
12/27/2021
12/27/2021
12/27/2021
12/23/2021
12/23/2021
12/23/2021
12/23/2021
12/21/2021
12/17/2021
12/17/2021
12/17/2021
12/17/2021
12/16/2021
12/16/2021
12/16/2021
12/16/2021
12/14/2021
12/14/2021
12/14/2021
12/14/2021
12/13/2021
12/13/2021
12/13/2021
12/10/2021
12/10/2021
12/10/2021
12/10/2021
12/7/2021
12/10/2021
12/6/2021
12/6/2021
12/2/2021
12/2/2021
12/2/2021
12/1/2021
12/1/2021
12/1/2021
12/1/2021
12/1/2021
11/24/2021
11/24/2021
11/24/2021
11/24/2021
11/23/2021

2 Replies

# Re: Count Dates Per Month in a Range of random dates.

=SUMPRODUCT(--(MONTH(B8:B612)=12))

Both formulas work as expected in my sheet.

You can check if all cells in range B49:B612 are empty. If you enter e.g "tomorrow" in cell B222 the formula (  =SUMPRODUCT(--(MONTH(B8:B612)=12))  ) will return the #VALUE error.

# Re: Count Dates Per Month in a Range of random dates.

Thanks.

The problem ended up being related to the Date type format.
There were only two Date type formats that were not the same as the rest of the Range.
When I changed them to match the rest of the range, it worked fine.