Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
Hi Smart Folk:
I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
Hi,
Try this =SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5)) or if you want to include the employee =SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
SUMPRODUCT is the key to using multiple conditions.
=SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10) Note that you can add another criteria to check by Employee, if you wanted: =SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10) Just make sure that all the array sizes withing SUMPRODUCT are the same size. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
Thanks so much...................can I use NOT EQUAL TO? Ex:
=SUMPRODUCT((B1:B5<"A")*(C1:C50)*(C1:C5)) I actually had more than 2 statuses.....I wanted a sum for all T's; then a sum for everything else. The < is only returning a value of zero.......which I don't think is right. -- ~~Boo "Mike H" wrote: Hi, Try this =SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5)) or if you want to include the employee =SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
I'm a goofball...........had a syntax error..........it worked
PERFECTLY.......thnx a bunch. -- ~~Boo "Booweezie" wrote: Thanks so much...................can I use NOT EQUAL TO? Ex: =SUMPRODUCT((B1:B5<"A")*(C1:C50)*(C1:C5)) I actually had more than 2 statuses.....I wanted a sum for all T's; then a sum for everything else. The < is only returning a value of zero.......which I don't think is right. -- ~~Boo "Mike H" wrote: Hi, Try this =SUMPRODUCT((B1:B5="A")*(C1:C50)*(C1:C5)) or if you want to include the employee =SUMPRODUCT((A1:A5="EMP #1")*(B1:B5="A")*(C1:C50)*(C1:C5)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
is there a COUNTPRODUCT function? I now find that I need to count the number
of occurrences that were summed in my example. -- ~~Boo "Luke M" wrote: SUMPRODUCT is the key to using multiple conditions. =SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10) Note that you can add another criteria to check by Employee, if you wanted: =SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10) Just make sure that all the array sizes withing SUMPRODUCT are the same size. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
is there a COUNTPRODUCT function? I now find that I need to count the
number of occurrences that were summed in my example. =SUMPRODUCT((B1:B5="A")*(C1:C50)) should do that -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Booweezie" wrote: is there a COUNTPRODUCT function? I now find that I need to count the number of occurrences that were summed in my example. -- ~~Boo "Luke M" wrote: SUMPRODUCT is the key to using multiple conditions. =SUMPRODUCT(--(B2:B10="A"),--(C2:C100),C2:C10) Note that you can add another criteria to check by Employee, if you wanted: =SUMPRODUCT(--(A2:A10="Emp #1"),--(B2:B10="A"),--(C2:C100),C2:C10) Just make sure that all the array sizes withing SUMPRODUCT are the same size. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Booweezie" wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. -- ~~Boo |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
Booweezie wrote:
is there a COUNTPRODUCT function? I now find that I need to count the number of occurrences that were summed in my example. Look he http://www.contextures.com/xlFunctio...tml#SumProduct |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex conditions using SUMIF
Complex conditions using SUMIF
As a side note, Sumproduct is the most popular, but Excel 2007 has the newer "SumIfs" function. I prefer to use the heading row for Named Ranges. For Example... =SUMIFS(Amount, Status, "A", Amount, "0") or =SUMIFS(Amount, Status, "<A", Amount, "0") = = = = = = = HTH :) Dana DeLouis On 2/15/2010 12:26 PM, Booweezie wrote: Hi Smart Folk: I need to analyze employee date and am having problems. Here's my data: Employee Number Status Amount --------------------- ------- --------- Emp #1 A $3.00 Emp #1 T ($2.25) Emp #1 T $14.00 Emp #1 A ($7.50) Emp #1 A $1.30 I need to know the sum of Amount by employment status ONLY if the amount is greater than zero. The answers for this table that I want to get a A = $4.30 T = $14.00 I can use the SUMIF function to get a total for A's and T's; but I'm lost on how I can limit it only to amounts greater than zero. Thanks for any help you can provide. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
Complex VBA Sumif | Excel Discussion (Misc queries) | |||
complex SUMIF | Excel Worksheet Functions | |||
need help COMPLEX SUM /w conditions | Excel Worksheet Functions | |||
need help COMPLEX SUM /w conditions | Excel Worksheet Functions |