Hi Tendresse,

Here is a couple of options:-

You can dummy a date from Jan by concatenating it with day of month and a
year. It doesn't really matter what day of month or year you use. The
following is for a locale that uses default d/m/y format.

=MONTH("1-"&D2&"-2007")

If you use m/d/y format then you might have to use :-
=MONTH(D2&"-1"&"-2007")

Another option is to format column D as date with custom format set to "mmm"
so that you have an actual date but only display the month. You can then use:-

=MONTH(D2)

Regards,

OssieMac


"Tendresse" wrote:

Hi all, it may be a simple question but i need some guidance.
I have a table where Column A is used to enter dates (I called that range
'Date'), and Column B is used to enter employee names (I called that range
'Empl').

Outside that table, using Data Validation, I created a drop down list in
cell D1 of the 12 months (Jan, Feb, etc). In cells E1 to E5 I listed the
employee names, then in cells F1 to F5, I have a formula that counts the
number of times the name of each employee appeared in each month.

A B C D E F
Date Empl. Jan Ann =SUMPRODUCT(--(MONTH(Date)=i),--(Empl="Ann")
1/2/07 Paul Neil =SUMPRODUCT(--(MONTH(Date)=i),--(Empl="Neil")
3/2/07 Neil Paul etc
5/3/07 Ann Sam etc
6/3/07 Ann Suzy etc

Now time for my question.
'i' is a variable integer (1 to 12) that represents that month number (Jan =
1, Feb = 2, etc).
How can i change the value of 'i' in all the formulas to correspond with the
month selected from the list in cell D1. For example, if September is
selected, i want the formula to be:
=SUMPRODUCT(--(MONTH(Date)=9),--(Empl="whatever")

I'm using Excel 2003
thank u in advance
tendresse