Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct problem?
=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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct problem?
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" wrote in message : =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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct problem?
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" 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" wrote in message : =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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct problem?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct problem?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using ADDRESS() in SUMPRODUCT() | Excel Discussion (Misc queries) | |||
Frustrating SUMPRODUCT problem. | Excel Discussion (Misc queries) | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT problem | Excel Worksheet Functions |