sumproduct problem?

i'm using the sumproduct formula to pick up sick days for people within an
area which works fine for one criteria.

=SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))

I need the formula to pick up sick, bank holidays and annual leave days and
add them all together for each area. is this possible?

Thanks
Tolga

=SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
$16="area1"),($D$5:$D$16))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tolga" <[email protected]> wrote in message
news:[email protected]...
> i'm using the sumproduct formula to pick up sick days for people within an
> area which works fine for one criteria.
>
> =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
>
> I need the formula to pick up sick, bank holidays and annual leave days
and
> add them all together for each area. is this possible?
>
> Thanks
> Tolga
>

Since it doesn't seem to matter whether you are sick, bh or leave,
Just use SumIf using area1 as criteria.

=SUMIF(E5:E16,"area1",D5:D16)



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]:

> =SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
> $16="area1"),($D$5:$D$16))
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Tolga" <[email protected]> wrote in message
> news:[email protected]...
> > i'm using the sumproduct formula to pick up sick days for people within an
> > area which works fine for one criteria.
> >
> > =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
> >
> > I need the formula to pick up sick, bank holidays and annual leave days
> and
> > add them all together for each area. is this possible?
> >
> > Thanks
> > Tolga
> >

There are probably blanks or something else Jim to indicate attendance.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jim May" <[email protected]> wrote in message
news:cwNqg.51856$fG3.29160@dukeread09...
> Since it doesn't seem to matter whether you are sick, bh or leave,
> Just use SumIf using area1 as criteria.
>
> =SUMIF(E5:E16,"area1",D5:D16)
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]:
>
> >
=SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
> > $16="area1"),($D$5:$D$16))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Tolga" <[email protected]> wrote in message
> > news:[email protected]...
> > > i'm using the sumproduct formula to pick up sick days for people
within an
> > > area which works fine for one criteria.
> > >
> > > =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
> > >
> > > I need the formula to pick up sick, bank holidays and annual leave
days
> > and
> > > add them all together for each area. is this possible?
> > >
> > > Thanks
> > > Tolga
> > >
>

Thanks for the quick response however, it's not working how i'd like it to be.

As well as adding sick, bh, leave together. it's also adding regular time
even though it's not part of the criteria. I've incorrectly spelt holiday in
the formula to see if it's taking notice of the criteria and it still picks
it up the data.

Any ideas?

"Bob Phillips" wrote:

> =SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
> $16="area1"),($D$5:$D$16))
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Tolga" <[email protected]> wrote in message
> news:[email protected]...
> > i'm using the sumproduct formula to pick up sick days for people within an
> > area which works fine for one criteria.
> >
> > =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
> >
> > I need the formula to pick up sick, bank holidays and annual leave days
> and
> > add them all together for each area. is this possible?
> >
> > Thanks
> > Tolga
> >
>
>
>

No it doesn't, I have just tried it again.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Tolga" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the quick response however, it's not working how i'd like it to
be.
>
> As well as adding sick, bh, leave together. it's also adding regular time
> even though it's not part of the criteria. I've incorrectly spelt holiday
in
> the formula to see if it's taking notice of the criteria and it still
picks
> it up the data.
>
> Any ideas?
>
> "Bob Phillips" wrote:
>
> >
=SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
> > $16="area1"),($D$5:$D$16))
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Tolga" <[email protected]> wrote in message
> > news:[email protected]...
> > > i'm using the sumproduct formula to pick up sick days for people
within an
> > > area which works fine for one criteria.
> > >
> > > =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
> > >
> > > I need the formula to pick up sick, bank holidays and annual leave
days
> > and
> > > add them all together for each area. is this possible?
> > >
> > > Thanks
> > > Tolga
> > >
> >
> >
> >

just done it. The MATCH wasn't closed off with 0.

=SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"},0))),--($E$5:$E$16="area1"),($D$5:$D$16))

Thanks for all your help.

"Bob Phillips" wrote:

> No it doesn't, I have just tried it again.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Tolga" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the quick response however, it's not working how i'd like it to
> be.
> >
> > As well as adding sick, bh, leave together. it's also adding regular time
> > even though it's not part of the criteria. I've incorrectly spelt holiday
> in
> > the formula to see if it's taking notice of the criteria and it still
> picks
> > it up the data.
> >
> > Any ideas?
> >
> > "Bob Phillips" wrote:
> >
> > >
> =SUMPRODUCT(--(ISNUMBER(MATCH($C$5:$C$16,{"sick","bh","leave"}))),--($E$5:$E
> > > $16="area1"),($D$5:$D$16))
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (replace somewhere in email address with gmail if mailing direct)
> > >
> > > "Tolga" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > i'm using the sumproduct formula to pick up sick days for people
> within an
> > > > area which works fine for one criteria.
> > > >
> > > > =SUMPRODUCT(--($C$5:$C$16="sick"),--($E$5:$E$16="area1"),($D$5:$D$16))
> > > >
> > > > I need the formula to pick up sick, bank holidays and annual leave
> days
> > > and
> > > > add them all together for each area. is this possible?
> > > >
> > > > Thanks
> > > > Tolga
> > > >
> > >
> > >
> > >
>
>
>