0

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?

2

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.

1

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.

0

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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.