+ Reply to Thread
Results 1 to 7 of 7

SUM IF and two variables

  1. #1
    Leigh Ann
    Guest

    SUM IF and two variables

    I have tried...without success.

    A B C D
    dog 1 brown 2
    cat 1 brown
    dog 2 black 4
    cat 3 blue

    For example: I need a formula that says if column D has a value in it AND
    column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT
    function but cannot solve it. The problem is that column D cells contain a
    formula, I think. Any help is greatly appreciated.


  2. #2
    N Harkawat
    Guest

    Re: SUM IF and two variables

    =SUMPRODUCT(--(D1:D4<>""),--(A1:A4="dog"),(B1:B4))

    "Leigh Ann" <[email protected]> wrote in message
    news:[email protected]...
    >I have tried...without success.
    >
    > A B C D
    > dog 1 brown 2
    > cat 1 brown
    > dog 2 black 4
    > cat 3 blue
    >
    > For example: I need a formula that says if column D has a value in it AND
    > column A = "dog", then sum column B. I have tried versions of the
    > SUMPRODUCT
    > function but cannot solve it. The problem is that column D cells contain
    > a
    > formula, I think. Any help is greatly appreciated.
    >




  3. #3
    Duke Carey
    Guest

    RE: SUM IF and two variables

    If the formula in D is returning an empty string (""), then this will work

    =SUMPRODUCT(--(A32:A35="dog"),--(LEN(D32:D35)>0),B32:B35)

    If it's returning a 0 that is formatted to not show then

    =SUMPRODUCT(--(A1:A5="dog"),--(D1:D5>0),B1:B5)


    "Leigh Ann" wrote:

    > I have tried...without success.
    >
    > A B C D
    > dog 1 brown 2
    > cat 1 brown
    > dog 2 black 4
    > cat 3 blue
    >
    > For example: I need a formula that says if column D has a value in it AND
    > column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT
    > function but cannot solve it. The problem is that column D cells contain a
    > formula, I think. Any help is greatly appreciated.
    >


  4. #4
    Leigh Ann
    Guest

    RE: SUM IF and two variables

    Thanks, Duke - that worked!

    "Duke Carey" wrote:

    > If the formula in D is returning an empty string (""), then this will work
    >
    > =SUMPRODUCT(--(A32:A35="dog"),--(LEN(D32:D35)>0),B32:B35)
    >
    > If it's returning a 0 that is formatted to not show then
    >
    > =SUMPRODUCT(--(A1:A5="dog"),--(D1:D5>0),B1:B5)
    >
    >
    > "Leigh Ann" wrote:
    >
    > > I have tried...without success.
    > >
    > > A B C D
    > > dog 1 brown 2
    > > cat 1 brown
    > > dog 2 black 4
    > > cat 3 blue
    > >
    > > For example: I need a formula that says if column D has a value in it AND
    > > column A = "dog", then sum column B. I have tried versions of the SUMPRODUCT
    > > function but cannot solve it. The problem is that column D cells contain a
    > > formula, I think. Any help is greatly appreciated.
    > >


  5. #5
    Mike
    Guest

    Re: SUM IF and two variables

    Duke,

    Can you explain the purpose of the double negatives?

    Thanks.


  6. #6
    Domenic
    Guest

    Re: SUM IF and two variables

    In article <[email protected]>,
    "Mike" <[email protected]> wrote:

    > Can you explain the purpose of the double negatives?


    Have a look at the following...

    http://www.mcgimpsey.com/excel/formulae/doubleneg.html

  7. #7
    Mike
    Guest

    Re: SUM IF and two variables

    Got it! Thanks for the post.


+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1