sumproduct 3 criteria inc. date

[Guest]

byI have set up successful SUMPRODUCT to count the number of people in a
particular job, by depot, and where the 'leave date' column is empty.

I would like to count the number of people in a particular job, by depot, by
month.

The value in the 'startdate' column is a date dd/mm/yyyy.

I have tried to follow the answers given in previous posts, with no luck.

Many thanks

 

[Guest]

If the dates are in column I, then create a "helper" column with formulas like:
=MONTH(I1)

and then use this column in your SUMPRODUCT.

 

[Guest]

Thanks for speedy re;y! If I use the helper column, how do I get the
Sumproduct to filter out only one month?

D

 

[Guest]

When I enter =MONTH(H3) the date in H3 shows as Jan-00 when the actual date
is 27/04/2007 - any ideas?

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun"))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

That is because the result field is formatted as a date, day1 is 1st Jan
1900

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

As always I am in awe of your knowledge...

I am getting a result, but not the correct one. I am now using a column for
the start date which is dd/mm/yyyy and I need to filter out by month and
year, ie.e find all starters for a job and depot for April 2007.

Many, many thanks for your generous help.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmmyyyy")="Apr2007"))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

A thousand thanks - that works, even when the date cells are formatted
d/mmm/yyyy.

D