Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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]<>""))
share|improve this question
3  
Welcome to superuser. Can you show us an anonymized example spreadsheet? –  nixda Feb 13 '13 at 0:32
add comment

1 Answer

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))

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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