Hi OssieMac, thank you very much for your reply. You surely put me one step
closer, but i'm still getting an error. I'm must be still missing something.

I formatted cell D1 as custom mmm
and put the following formula in cell F1:
=SUMPRODUCT(--(MONTH(Date)=MONTH($D$1)),--(Empl="Ann"))

the error i'm getting is: A value used in the formula is of the wrong data
type.

what am i doing wrong?
Thanks again for your help, much appreciated


"OssieMac" wrote:

Hi again Tendresse

I am not sure that I really explained myself properly the the previous post.
The reference to the cell with the name of the month in the formula should be
absolute if it always refers to D1. That is $D$1 and you replace 'i' in your
formula with the formula I have given you.

"OssieMac" wrote:

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