View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.misc
Tolga Tolga is offline
external usenet poster
 
Posts: 5
Default sumproduct problem?

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