| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding 2 Values When 3 Conditions are Met, Including an "OR"
I need to add up dollar amounts in Column D when Columns A and B meet certain
critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Verbal"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Closed"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
That returns 400 for me. Am I doing something wrong? Anne M
"Excelenator" wrote: Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Verbal"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
Ah, I wondered if that was it...was just trying the change. Thanks !
"Excelenator" wrote: Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Closed"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
I'm sorry, but when I use this format for my 'real' data, it returns #VALUE!.
I modified my example for simplicity - thinking I could follow the format and all would be well.....but appears not. Here is my true formula. =SUMPRODUCT(('Raw Data'!A2:A477="Open")*('Raw Data'!I2:I477="Cerner")*(('Raw Data'!J2:J477="Verbal")+('Raw Data'!J2:J477="Closed"))*('Raw Data'!L2:L477)) Aren't I using the correct syntax? Sorry to be a problem, Anne M "Excelenator" wrote: Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Closed"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
I think the blanks are what is causing this problem. Can you select the entire range of data press F5 and click "Special" and select the "Blanks" radio button and click "OK"? Once that is done type in a single quote or zeror and hit CTL-ENTER. This will put a single quote mark or zeror (or any other representation you want for blank cells) in all the blank cells. Then Sumproduct should work. -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
Try this:
=SUMPRODUCT((A2:A477="Open")*(I2:I477="Cerner")*(J 2:J477={"Verbal","Closed"})*L2:L477) You can carefully add your paths. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "annem" wrote in message ... I'm sorry, but when I use this format for my 'real' data, it returns #VALUE!. I modified my example for simplicity - thinking I could follow the format and all would be well.....but appears not. Here is my true formula. =SUMPRODUCT(('Raw Data'!A2:A477="Open")*('Raw Data'!I2:I477="Cerner")*(('Raw Data'!J2:J477="Verbal")+('Raw Data'!J2:J477="Closed"))*('Raw Data'!L2:L477)) Aren't I using the correct syntax? Sorry to be a problem, Anne M "Excelenator" wrote: Use this =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6="Closed"))*(D2:D6)) annem Wrote: I need to add up dollar amounts in Column D when Columns A and B meet certain critieria and Column C meets either of two conditions. The following should return 300 A B C D Open Alabama Closed 100 Open Georgia Verbal 200 Open Alabama Verbal 100 Open Alabama Open Alabama Verbal 100 I can do it with Column C meeting 1 condition, but can't figure out how to include the other condition. =SUMPRODUCT(IF(A2:A6="Open",IF(B2:B6="Alabama",IF( C2:C5="Verbal",D2:D6))) works BUT I need to add the condition "If C2:C6="Closed". i.e. I want to sum the dollar amounts for all Open Accounts in Alablama that are either Closed or Verbal. Sometimes Columns C and D are blank. Thanks, Anne M -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding 2 Values When 3 Conditions are Met, Including an "OR"
Sorry the formula I posted was =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6=*"Verbal"*))*(D2:D6)) And it should have been =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2: C6="Verbal") + (C2:C6=*"Closed"*))*(D2:D6)) -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566632 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding name values | Excel Worksheet Functions | |||
| Help adding text values | Excel Worksheet Functions | |||
| Adding logical values | Excel Discussion (Misc queries) | |||
| Adding multiple cells, return specific values | Excel Worksheet Functions | |||
| time format and adding tiome values. | Excel Worksheet Functions | |||
Linear Mode
