|
Conditional summing when more than one condition must be met
Hi, does anyone know if there's a formula that will allow you to sum based on
more than one condition: e.g I'm trying to sum the number of Ordinary hours for one particular employee. So the sum equation would need to be sumif(employee name=Beavis Trevor John AND payrate=ordinary) Cost Code Employee Name Pay Rate Hours 12200 Beavis, Trevor John OT1.5 2.7 12200 Elliott, Stewart Middleton OT1.5 4 12200 Horvath, Daniel Michael OT1.5 10 12200 Horvath, Daniel Michael OT1.5 2 12200 Watt, Andrew OT1.5 6.7 12200 Weary, Shane Patrick OT1.5 4 12200 Weary, Shane Patrick OT1.5 6 12200 Wilken, Allan OT1.5 2 12200 Horvath, Daniel Michael OT2 3 12200 Horvath, Daniel Michael OT2 0.5 12200 Pool, Luke Justin OT2 10 12200 Tuffin, Scott Edward OT2 1 12200 Watt, Andrew OT2 3 12200 Weary, Shane Patrick OT2 10.5 12200 Beavis, Trevor John ORDINARY 19.5 12200 Elliott, Stewart Middleton ORDINARY 20 12200 Evans, Paul Ashley ORDINARY 4 12200 Horvath, Daniel Michael ORDINARY 32 12200 Melkus, Christopher Martin ORDINARY 5 12200 Pool, Luke Justin ORDINARY 34 Is this possible? -- Jo :) |
Conditional summing when more than one condition must be met
Hi
For instance: =sumproduct((A1:A10="something")*(D1:D10="somethin g else")) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Jo" wrote: Hi, does anyone know if there's a formula that will allow you to sum based on more than one condition: e.g I'm trying to sum the number of Ordinary hours for one particular employee. So the sum equation would need to be sumif(employee name=Beavis Trevor John AND payrate=ordinary) Cost Code Employee Name Pay Rate Hours 12200 Beavis, Trevor John OT1.5 2.7 12200 Elliott, Stewart Middleton OT1.5 4 12200 Horvath, Daniel Michael OT1.5 10 12200 Horvath, Daniel Michael OT1.5 2 12200 Watt, Andrew OT1.5 6.7 12200 Weary, Shane Patrick OT1.5 4 12200 Weary, Shane Patrick OT1.5 6 12200 Wilken, Allan OT1.5 2 12200 Horvath, Daniel Michael OT2 3 12200 Horvath, Daniel Michael OT2 0.5 12200 Pool, Luke Justin OT2 10 12200 Tuffin, Scott Edward OT2 1 12200 Watt, Andrew OT2 3 12200 Weary, Shane Patrick OT2 10.5 12200 Beavis, Trevor John ORDINARY 19.5 12200 Elliott, Stewart Middleton ORDINARY 20 12200 Evans, Paul Ashley ORDINARY 4 12200 Horvath, Daniel Michael ORDINARY 32 12200 Melkus, Christopher Martin ORDINARY 5 12200 Pool, Luke Justin ORDINARY 34 Is this possible? -- Jo :) |
Conditional summing when more than one condition must be met
For the name being in B2, enter in E2:
=SUMPRODUCT(--($B$2:$B$21=B2),--($C$2:$C$21="ORDINARY"),$D$2:$D$21) Regards, Stefi €žJo€ ezt Ã*rta: Hi, does anyone know if there's a formula that will allow you to sum based on more than one condition: e.g I'm trying to sum the number of Ordinary hours for one particular employee. So the sum equation would need to be sumif(employee name=Beavis Trevor John AND payrate=ordinary) Cost Code Employee Name Pay Rate Hours 12200 Beavis, Trevor John OT1.5 2.7 12200 Elliott, Stewart Middleton OT1.5 4 12200 Horvath, Daniel Michael OT1.5 10 12200 Horvath, Daniel Michael OT1.5 2 12200 Watt, Andrew OT1.5 6.7 12200 Weary, Shane Patrick OT1.5 4 12200 Weary, Shane Patrick OT1.5 6 12200 Wilken, Allan OT1.5 2 12200 Horvath, Daniel Michael OT2 3 12200 Horvath, Daniel Michael OT2 0.5 12200 Pool, Luke Justin OT2 10 12200 Tuffin, Scott Edward OT2 1 12200 Watt, Andrew OT2 3 12200 Weary, Shane Patrick OT2 10.5 12200 Beavis, Trevor John ORDINARY 19.5 12200 Elliott, Stewart Middleton ORDINARY 20 12200 Evans, Paul Ashley ORDINARY 4 12200 Horvath, Daniel Michael ORDINARY 32 12200 Melkus, Christopher Martin ORDINARY 5 12200 Pool, Luke Justin ORDINARY 34 Is this possible? -- Jo :) |
Conditional summing when more than one condition must be met
YAY it works THANK YOU! :)
-- Jo :) "Stefi" wrote: For the name being in B2, enter in E2: =SUMPRODUCT(--($B$2:$B$21=B2),--($C$2:$C$21="ORDINARY"),$D$2:$D$21) Regards, Stefi €žJo€ ezt Ã*rta: Hi, does anyone know if there's a formula that will allow you to sum based on more than one condition: e.g I'm trying to sum the number of Ordinary hours for one particular employee. So the sum equation would need to be sumif(employee name=Beavis Trevor John AND payrate=ordinary) Cost Code Employee Name Pay Rate Hours 12200 Beavis, Trevor John OT1.5 2.7 12200 Elliott, Stewart Middleton OT1.5 4 12200 Horvath, Daniel Michael OT1.5 10 12200 Horvath, Daniel Michael OT1.5 2 12200 Watt, Andrew OT1.5 6.7 12200 Weary, Shane Patrick OT1.5 4 12200 Weary, Shane Patrick OT1.5 6 12200 Wilken, Allan OT1.5 2 12200 Horvath, Daniel Michael OT2 3 12200 Horvath, Daniel Michael OT2 0.5 12200 Pool, Luke Justin OT2 10 12200 Tuffin, Scott Edward OT2 1 12200 Watt, Andrew OT2 3 12200 Weary, Shane Patrick OT2 10.5 12200 Beavis, Trevor John ORDINARY 19.5 12200 Elliott, Stewart Middleton ORDINARY 20 12200 Evans, Paul Ashley ORDINARY 4 12200 Horvath, Daniel Michael ORDINARY 32 12200 Melkus, Christopher Martin ORDINARY 5 12200 Pool, Luke Justin ORDINARY 34 Is this possible? -- Jo :) |
Conditional summing when more than one condition must be met
You are welcome! Thanks for the feedback!
Stefi €žJo€ ezt Ã*rta: YAY it works THANK YOU! :) -- Jo :) "Stefi" wrote: For the name being in B2, enter in E2: =SUMPRODUCT(--($B$2:$B$21=B2),--($C$2:$C$21="ORDINARY"),$D$2:$D$21) Regards, Stefi €žJo€ ezt Ã*rta: Hi, does anyone know if there's a formula that will allow you to sum based on more than one condition: e.g I'm trying to sum the number of Ordinary hours for one particular employee. So the sum equation would need to be sumif(employee name=Beavis Trevor John AND payrate=ordinary) Cost Code Employee Name Pay Rate Hours 12200 Beavis, Trevor John OT1.5 2.7 12200 Elliott, Stewart Middleton OT1.5 4 12200 Horvath, Daniel Michael OT1.5 10 12200 Horvath, Daniel Michael OT1.5 2 12200 Watt, Andrew OT1.5 6.7 12200 Weary, Shane Patrick OT1.5 4 12200 Weary, Shane Patrick OT1.5 6 12200 Wilken, Allan OT1.5 2 12200 Horvath, Daniel Michael OT2 3 12200 Horvath, Daniel Michael OT2 0.5 12200 Pool, Luke Justin OT2 10 12200 Tuffin, Scott Edward OT2 1 12200 Watt, Andrew OT2 3 12200 Weary, Shane Patrick OT2 10.5 12200 Beavis, Trevor John ORDINARY 19.5 12200 Elliott, Stewart Middleton ORDINARY 20 12200 Evans, Paul Ashley ORDINARY 4 12200 Horvath, Daniel Michael ORDINARY 32 12200 Melkus, Christopher Martin ORDINARY 5 12200 Pool, Luke Justin ORDINARY 34 Is this possible? -- Jo :) |
| All times are GMT +1. The time now is 12:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com