User profile for user: SansSuit
SansSuit Author
User level: Level 1
8 points

Count date occurences by month and year

Greetings!


I have a column that contains thousands of dates. I need to count the number of dates that are in a given month and year.


In Excel, I use this formula to count the number of dates that are June 2020:

=SUMPRODUCT(1*(YEAR(date)=2020),(1*(MONTH(date)=6))) "date" is the named range for the column of dates.


When I opened up the spreadsheet in Numbers, this is the converted formula:

SUMPRODUCT(1×(YEAR(DATA::Table 1::$C$2:$C$2470)=2020),(1×(MONTH(DATA::Table 1::$C$2:$C$2470)=6))) "DATA::Table 1::$C$2:$C$2470" is the sheet and column with the dates.


This produces incorrect results and a message "The formula uses a Boolean in place of a number.".


Could someone please help me out? I'd appreciate it.


This conversion is the first time I've been exposed to Numbers. For the record, I did do a search before asking the question.


Thanks!

iMac 21.5″, macOS 10.15

Posted on Aug 10, 2020 12:49 PM

Reply
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
37,592 points

Posted on Aug 10, 2020 9:18 PM

That's a special "array formula" form of SUMPRODUCT that Numbers doesn't support.


Here are three ways you count the dates in Jun with Numbers.




The first way is to add a year column and a month column and use COUNTIFS to do your count.


The second way, which doesn't need extra columns is to use a formula like this:


=COUNTIFS(Table 1::A,">="&DATE(2020,6,1),Table 1::A,"<="&DATE(2020,6,30))


Where the dates are in column A. (Note that in Numbers you reference an entire column with A, whereas in Excel you need to use A:A. In Numbers generally it is more efficient to just click the range you want to reference when building a formula in the formula editor, and let Numbers do the rest.


The third way doesn't need extra columns or even a formula. You go to the letter at the top of the column with dates (in my example A and choose Add Category for "Date". This groups the data automatically into months, something like this:



Then go to the "gear" icon in one of the groups and choose 'Count'.


If you want you can then Collapse Peer Groups to get a summary by month:





All in seconds with a few clicks, without even entering a formula.


SG


View in context
3 replies
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
37,592 points

Aug 10, 2020 9:18 PM in response to SansSuit

That's a special "array formula" form of SUMPRODUCT that Numbers doesn't support.


Here are three ways you count the dates in Jun with Numbers.




The first way is to add a year column and a month column and use COUNTIFS to do your count.


The second way, which doesn't need extra columns is to use a formula like this:


=COUNTIFS(Table 1::A,">="&DATE(2020,6,1),Table 1::A,"<="&DATE(2020,6,30))


Where the dates are in column A. (Note that in Numbers you reference an entire column with A, whereas in Excel you need to use A:A. In Numbers generally it is more efficient to just click the range you want to reference when building a formula in the formula editor, and let Numbers do the rest.


The third way doesn't need extra columns or even a formula. You go to the letter at the top of the column with dates (in my example A and choose Add Category for "Date". This groups the data automatically into months, something like this:



Then go to the "gear" icon in one of the groups and choose 'Count'.


If you want you can then Collapse Peer Groups to get a summary by month:





All in seconds with a few clicks, without even entering a formula.


SG


Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Count date occurences by month and year

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up