sumproduct 3 criteria inc. date
 

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

sumproduct 3 criteria inc. date
 

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.
--
Gary''s Student - gsnu200730


"D" wrote:

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

sumproduct 3 criteria inc. date
 

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

D

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200730


"D" wrote:

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

sumproduct 3 criteria inc. date
 

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

"D" wrote:

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

sumproduct 3 criteria inc. date
 

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

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

sumproduct 3 criteria inc. date
 

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)

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

"D" wrote:

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

sumproduct 3 criteria inc. date
 

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" wrote:

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

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

sumproduct 3 criteria inc. date
 

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

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

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

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

sumproduct 3 criteria inc. date
 

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

D

"Bob Phillips" wrote:

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

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

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

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