OOps ... you are right. Thank you very much, T. Valko for bringing this point
to my attention.

First solution that came to my mind now is to alter the formula as follows:

=IF(date="","",SUMPRODUCT(--(MONTH(date)=MONTH($D$1)),--(empl="Ann"))

Would this solve the problem you think?

I don't quite understand the bit you added to the formula: "01-"&.
Can you please clarify what it does?

(Just in case it makes a difference, I formatted the cell D1 as mmm-yy
instead of mmm)

Another question crossed my mind now. is it ok to name a range 'Date'? I
just remembered that 'DATE' is a built-in keyword in Excel formulas!

Thanks again for your input.
cheers, Tendresse



"T. Valko" wrote:

Try it like this:

=SUMPRODUCT(--(MONTH(date)=MONTH("01-"&D$1)),--(empl="Ann"))

Note that if you have empty cells in the date range they will evaluate as
month 1.

--
Biff
Microsoft Excel MVP


"Tendresse" wrote in message
...
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