sumproduct 3 criteria inc. date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D - 17 Jun 2007 17:35 GMT

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

Reply to this Message

Gary''s Student - 17 Jun 2007 17:40 GMT

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.

Signature

Gary''s Student - gsnu200730

> 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.
[quoted text clipped - 7 lines]
>
> Many thanks

Reply to this Message

D - 17 Jun 2007 17:49 GMT

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

D

> If the dates are in column I, then create a "helper" column with formulas like:
> =MONTH(I1)
[quoted text clipped - 12 lines]
> >
> > Many thanks

Reply to this Message

D - 17 Jun 2007 17:53 GMT

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

> 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.
[quoted text clipped - 7 lines]
>
> Many thanks

Reply to this Message

Bob Phillips - 17 Jun 2007 18:04 GMT

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

Signature

HTH

Bob

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

> When I enter =MONTH(H3) the date in H3 shows as Jan-00 when the actual
> date
[quoted text clipped - 12 lines]
>>
>> Many thanks

Reply to this Message

Bob Phillips - 17 Jun 2007 17:58 GMT

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

Signature

HTH

Bob

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

> 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.
[quoted text clipped - 8 lines]
>
> Many thanks

Reply to this Message

D - 17 Jun 2007 18:09 GMT

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.

> =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmm")="Jun"))
>
[quoted text clipped - 10 lines]
> >
> > Many thanks

Reply to this Message

Bob Phillips - 17 Jun 2007 18:36 GMT

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

Signature

HTH

Bob

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

> As always I am in awe of your knowledge...
>
[quoted text clipped - 22 lines]
>> >
>> > Many thanks

Reply to this Message

D - 17 Jun 2007 19:27 GMT

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

D

> =SUMPRODUCT(--(A2:A200="dept"),--(B2:B200="job"),--(TEXT(C2:C200,"mmmyyyy")="Apr2007"))
>
[quoted text clipped - 24 lines]
> >> >
> >> > Many thanks

Reply to this Message