Array??

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Hi Tendresse,

You have substitued 'i 'with the formula that I gave you correctly. However,
the example you have posted appears to use literals to explain what you are
trying to achieve. For instance where you have the word 'Date', I thought
probably refererred to a cell reference in column a with the date in it.

Perhaps if you copy your entire formula as you are trying to insert it and
post it and I will see if I can pick the error. (To copy the formula select
it in the formula bar and copy).

We will win eventually.

Regards,

OssieMac

 

[Guest]

Hi OssieMac,
We won! .. i figured out what i did wrong.

First, to answer your question. 'Date' is a name that i gave to the range of
cells containing the dates in column A. Similarly, 'Empl' is a name i gave to
the range of cells containing employee names in column B.

My mistake is that I included the column heading in the 'Date' range, and
didn't do the same in the 'Empl' range. Therefore, one range was longer than
the other. The formula worked perfectly once i adjusted both ranges so that
they started from the same row and ended in the same row.

Thank you again OssieMac .. you have been of great help.
Have a wonderful week-end (that's if by chance 'Ossie' refers to Australia)

 

[T. Valko]

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.

 

[Guest]

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

 

[Guest]

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

 

[Roger Govier]

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"))

 

[T. Valko]

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

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

I read your post to mean that cell D1 is a drop down list which contains the
TEXT names of the months: Jan, Feb, Mar, etc.

MONTH("01-"&D$1)

That expression will return the month number based on the month TEXT name in
cell D1. If D1 holds the TEXT entry Dec then:

MONTH("01-"&D$1) = 12

Maybe I misunderstood!

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!

Yes, you can use DATE as a defined name.

 

[Guest]

Thanks for your reply, T. Valko.
Cell D1 is formatted as Date using the format mmm-yy, not as Text.
Cheers, Tendresse