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

Captain Crunch
Posts: 2
Joined: Mon Feb 12, 2024 3:30 pm

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

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!!

 Edit: Changed subject, was Determining number of entries per month from a list of dates  Make your post understandable by others  -- MrProgrammer, forum moderator
Attachments
SUMPRODUCT Test.ods
Last edited by MrProgrammer on Mon Feb 12, 2024 10:46 pm, edited 2 times in total.
OpenOffice 4.1.15 on Windows 10
MrProgrammer
Moderator
Posts: 4866
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Determining the number of entries per month from a list of dates

Hi, and welcome to the forum. Thank you for the attachment.
Captain Crunch wrote: Mon Feb 12, 2024 4:19 pm 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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Captain Crunch
Posts: 2
Joined: Mon Feb 12, 2024 3:30 pm

### Re: Determining the number of entries per month from a list of dates

MrProgrammer wrote: Mon Feb 12, 2024 4:44 pm 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
OpenOffice 4.1.15 on Windows 10