| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 :) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional summing - help | Excel Worksheet Functions | |||
| excel summing N largest values by condition | Excel Discussion (Misc queries) | |||
| Conditional summing | Excel Discussion (Misc queries) | |||
| Conditional Summing | Excel Discussion (Misc queries) | |||
| Summing an array based on text condition | Excel Discussion (Misc queries) | |||
Linear Mode
