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

Discuss the spreadsheet application
Post Reply
Captain Crunch
Posts: 2
Joined: Mon Feb 12, 2024 3:30 pm

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

Post by 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 
Attachments
SUMPRODUCT Test.ods
(11.46 KiB) Downloaded 27 times
Last edited by MrProgrammer on Mon Feb 12, 2024 10:46 pm, edited 2 times in total.
OpenOffice 4.1.15 on Windows 10
User avatar
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

Post by MrProgrammer »

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

Post by Captain Crunch »

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
Post Reply