Expert: Nathan Head - 5/22/2009

Question
I have failed to build a working formula for 2003, I have a spread sheet and in column E3:E99 I have entered dates (Appointments)-- on a separate summery sheet I would like to see how many appointments I have scheduled for the current month. I am looking to tie the "valid" count criteria to my computer clock (RTC). In other words I only want to only count the appointments I have in the current month when I open the spreadsheet. My current formatted date is MM/DD/YY. I do not want to count any dates in the selected cells unless they are in the current month and year. Or at least the current month. I am in real need of a sanity check.
The closest I have gotten to a working formula is
=SUMPRODUCT(MONTH(E3:E99=MONTH(NOW())) (This is obviously not correct)


Answer
Ron:

I apologize for the slightly slow response (I usually like to reply within a few hours; however, I was out of the office today running errands and I just got home).

You were VERY close with your formula - you definitely understood what needed to be done and were simply off on the syntax.  

There is a bad parenthesis placement and, based on how the formula works since it is a TRUE/FALSE situation, you need to multiply by 1 somewhere in the formula to convert the TRUES to 1 and the FALSES to 0.  Here is a revised formula:

=SUMPRODUCT((MONTH(E3:E99)=MONTH(NOW()))*1)

Now, if you want to ensure you only have appointments in the same year and month then you would want to use this function:

=SUMPRODUCT((MONTH(E3:E99)=MONTH(NOW()))*(YEAR(E3:E99)=YEAR(NOW()))*1)

Please let me know if you have any additional questions.