+ Reply to Thread
Results 1 to 8 of 8

Adding 2 Values When 3 Conditions are Met, Including an "OR"

  1. 07-31-2006, 09:48 AM #1
    annem
    Guest

    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
    Reply With QuoteRegister To Reply
  2. 07-31-2006, 10:18 AM #2
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    Use this

    =SUMPRODUCT((A2:A6="Open")*(B2:B6="Alabama")*((C2:C6="Verbal") + (C2:C6="Closed"))*(D2:D6))

    Quote Originally Posted by annem
    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
    ---------------------------------------------------
    ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
    To insert code into the VBE (Visual Basic Editor)
    1. Copy the code.
    2. Open workbook to paste code into.
    3. Right click any worksheet tab, select View Code
    4. VBE (Visual Basic Editor) opens to that sheets object
    5. You may change to another sheets object or the This Workbook object by double clicking it in the Project window
    6. In the blank space below the word "General" paste the copied code.
    Reply With QuoteRegister To Reply
  3. 07-31-2006, 10:38 AM #3
    annem
    Guest

    Re: 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
    >
    >
    Reply With QuoteRegister To Reply
  4. 07-31-2006, 10:48 AM #4
    annem
    Guest

    Re: 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
    >
    >
    Reply With QuoteRegister To Reply
  5. 07-31-2006, 11:03 AM #5
    annem
    Guest

    Re: 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
    >
    >
    Reply With QuoteRegister To Reply
  6. 07-31-2006, 03:46 PM #6
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    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.
    Reply With QuoteRegister To Reply
  7. 07-31-2006, 07:58 PM #7
    Ragdyer
    Guest

    Re: Adding 2 Values When 3 Conditions are Met, Including an "OR"

    Try this:

    =SUMPRODUCT((A2:A477="Open")*(I2:I477="Cerner")*(J2: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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >>
    >>

    Reply With QuoteRegister To Reply
  8. 08-02-2006, 09:45 AM #8
    Excelenator
    Excelenator is offline
    Valued Forum Contributor Excelenator's Avatar
    Join Date
    07-25-2006
    Location
    Wantagh, NY
    Posts
    333
    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))
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1