[Solved] Wrong count for December: SUMPRODUCT(MONTH(A1:A10)=12)

[Captain Crunch]

Hello,

This is driving me nuts!!

Column $A$1:$A$10 contains mostly non-consecutive dates. My actual spreadsheet allows for up to 100 rows, but I've only allowed for 10 in the attachment for demonstration purposes and only the first 9 are populated. Column E has 12 rows representing the months of the year. I want to determine how many dates are in each month. I'm using =SUMPRODUCT(MONTH($A$1:$A$10)=x) where x=1 for Jan, x=2 for Feb, etc. I intend having a different file for each year so the year is irrelevant.

The problem is that Jan to Nov function OK, but Dec returns the value 2 but there's only one Dec entry. This value seems to be the number of entries allowed in column A (10) minus the sum of Jan to Nov (8). All the formulae in column E are identical barring the value of x.

Help!!

Thanks in advance.

Edit: Changed subject, was Determining number of entries per month from a list of dates  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

Hi, and welcome to the forum. Thank you for the attachment.

The problem is that Jan to Nov function OK, but Dec returns the value 2 but there's only one Dec entry.

The reason is that MONTH(EmptyCell) is MONTH(0) which is MONTH("1899-12-30") and that date is in December. In E1 use =SUMPRODUCT(ISNUMBER($A$1:$A$10);MONTH(A$1:A$10)=ROWS(A$1:A1)) and fill down.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] The SUMPRODUCT function

[Captain Crunch]

Hi, and welcome to the forum. Thank you for the attachment.

The reason is that MONTH(EmptyCell) is MONTH(0) which is MONTH("1899-12-30") and that date is in December. In E1 use =SUMPRODUCT(ISNUMBER($A$1:$A$10);MONTH(A$1:A$10)=ROWS(A$1:A1)) and fill down.

Perfect!! Thank you so much MrProgrammer