How can I count the number of cells that match a given month in Numbers

Question

I'm using Apple numbers to run some calculations on CSV export data from Mailchimp.

The data is (loosely) like:

ID   Email,           SignupAt
01   me@example.com   2015-09-04
02   you@example.com  2015-09-05
03   him@example.com  2015-09-06
04   her@example.com  2015-10-04

I have another table where I want to count the number of records in this table by number. Something like:

                 September 2015 December 2015
New Subscribers:              3             1

I've tried the following formula (and various variations of it) in my spreadsheet, but keep getting the answer 0

=COUNTIF(TableA:SignupAt, (MONTH(TableA:SignupAt)=MONTH(B1) & YEAR(TableA:SignupAt)=YEAR(B1)

(Where B2 is the Column header for Table B that holds the dates September 2015)

What's the correct formula to count these records?

Answer 1

There is a simple solution to this using true and false values.

=SUM((MONTH(TableA:SignupAt)=MONTH(B1))*(YEAR(TableA:SignupaAt)=YEAR(B1)))

This works because only TRUE*TRUE = 1 and all other combinations return 0. So it counts the cells for which both conditions are true.

Also this is an array formula, so you have to press Ctrl+Shift+Enter after filling it in.

Answer 2

You have to use AND in the Formula not &, like the following:

=COUNTIF(TableA!C1:C4,AND(MONTH(TableA!C1:C4)=MONTH(B2),YEAR(TableA!C1:C4)=YEAR(B2))    

This is an array formula press Ctrl + Shift + Enter at the same time.

You can also use another formula:

=SUMPRODUCT((--YEAR(TableA!C1:C4)=YEAR(B2))*(--MONTH(TableA!C1:C4)=MONTH(B2)))

Again, you have to press Ctrl + Shift + Enter

Where C1:C4 is the column SignupAt and B2 is the Column header for Table B that holds the dates September 2015.

Make sure the date format in both tables is what Excel can understand.

Answer 3

You can come close to what you want with a simple Pivot table. But it won't update automatically.

If you want to use a formula, with your desired month/year in, for example, B9, and with that being the 1st of the month, formatted to display as you show, then use:

=COUNTIFS(TableA[SignupAt],">="&B9,TableA[SignupAt],"<="&EOMONTH(B9,0))

If that date in B9 might not necessarily be the first of the month, but some random date in that month, then the following formula would work:

=COUNTIFS(TableA[SignupAt],">"&B9-DAY(B9),TableA[SignupAt],"<="&EOMONTH(B9,0))

B9-DAY(B9) will always return the last date of the preceding month.