# monthly totals comparing two date columns, Excel 2010

How can I calculate a monthly total of the courses completed for the month ONLY when they were due for that same month (sometimes students are late and sometimes early)?

The spreadsheet has a column of 2013 expiration dates (column A in m/d/yyyy format), which also includes entries such as "New". Column B contains the 2014 due date (date given once course is completed for next years expiration, there are blank cells) in m/d/yyyy format.

I have tried the following formula that does not work.

``````=SUMPRODUCT((MONTH(R[-70]C:R[-13]C)=2)*(R[-70]C:R[-13]C<>""))--SUMPRODUCT((MONTH(R[-70]C[-1]:R[-13]C[-1])=2)*(R[-70]C[-1]:R[-13]C[-1]<>""))+SUMPRODUCT((MONTH(R[-9]C:R[-6]C)=2)*(R[-9]C:R[-6]C<>""))--SUMPRODUCT((MONTH(R[-9]C[-1]:R[-6]C[-1])=2)*(R[-9]C[-1]:R[-6]C[-1]<>""))
``````
-
Welcome to superuser. Can you show us an anonymized example spreadsheet? – nixda Feb 13 '13 at 0:32

I'm not sure why you are using RC notation....

I assume you want to count rows where col A is in Feb 2013 and column B is not blank - in Excel 2010 you can use COUNTIFS for that - put the first of the month (1-Feb-2013) in D2 and use this formula in E2

`=COUNTIFS(A:A,">="&D2,A:A,"<"&EDATE(D2,1),B:B,"<>")`

That will happily ignore any text like "New" in column A.....

....or does the column B entry have to be specifically Feb 2014? If so try

`=COUNTIFS(A:A,">="&D2,A:A,"<"&EDATE(D2,1),B:B,">="&EDATE(D2,12),B:B,"<"&EDATE(D2,13))`

-