Hi

Just add another condition to the Sumproduct formula to test for entries in
the Date column not being empty.

=SUMPRODUCT(--(MONTH(Date)=MONTH($D$1)),--(Date<""),--(Empl="Ann"))

--
Regards
Roger Govier



"Tendresse" wrote in message
...
I just realised how useless my rushed 'solution' is .. of course it didn't
work.

Just as i thought i had it all done! :)

Any ideas would be greatly appreciated

Tendresse

"Tendresse" wrote:

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