+ Reply to Thread
Results 1 to 8 of 8

want sumif function's range to evaluate 2 columns

  1. #1
    Debgala
    Guest

    want sumif function's range to evaluate 2 columns

    I wanted sumif to evaluate a range of cells over 2 columns.
    My formula looked like this
    =SUMIF(Q4:R27,">0",H4:H27)
    Oddly enough, it works if there is a value >0 in column Q,
    or in both columns Q and R,
    but will not work if there is a value >0 in column R but no value >0 in
    column Q.

    How can I make it work? (And if anyone has time, why doesn't it work the
    way it is now?)

    Thanks,
    Deb

  2. #2
    Bob Phillips
    Guest

    Re: want sumif function's range to evaluate 2 columns

    =SUMIF(Q4:Q27,">0",H4:H27)+SUMIF(R4:R27,">0",H4:H27)

    will work

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Debgala" <Debgala@discussions.microsoft.com> wrote in message
    news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
    > I wanted sumif to evaluate a range of cells over 2 columns.
    > My formula looked like this
    > =SUMIF(Q4:R27,">0",H4:H27)
    > Oddly enough, it works if there is a value >0 in column Q,
    > or in both columns Q and R,
    > but will not work if there is a value >0 in column R but no value >0 in
    > column Q.
    >
    > How can I make it work? (And if anyone has time, why doesn't it work the
    > way it is now?)
    >
    > Thanks,
    > Deb




  3. #3
    Domenic
    Guest

    Re: want sumif function's range to evaluate 2 columns

    Assuming that you want to sum the values in Column H where the
    corresponding values in Column Q or Column R are greater than zero,
    try...

    =SUMPRODUCT(--((Q4:Q27>0)+(R4:R27>0)>0),H4:H27)

    Hope this helps!

    In article <CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com>,
    "Debgala" <Debgala@discussions.microsoft.com> wrote:

    > I wanted sumif to evaluate a range of cells over 2 columns.
    > My formula looked like this
    > =SUMIF(Q4:R27,">0",H4:H27)
    > Oddly enough, it works if there is a value >0 in column Q,
    > or in both columns Q and R,
    > but will not work if there is a value >0 in column R but no value >0 in
    > column Q.
    >
    > How can I make it work? (And if anyone has time, why doesn't it work the
    > way it is now?)
    >
    > Thanks,
    > Deb


  4. #4
    RagDyeR
    Guest

    Re: want sumif function's range to evaluate 2 columns

    From just looking at your formula, I could guess that you want *both*
    Columns Q & R to be greater then zero before adding the value in Column H.

    However, I did say *guess*.

    Could it also be Q *OR* R greater then zero?

    So, is it Q *AND* R
    Or is it Q *OR* R ?

    And which way should Sumif() translate it?

    Q *and* R
    =SUMPRODUCT((Q4:Q27>0)*(R4:R27>0)*H4:H27)

    Q *or* R
    =SUMPRODUCT(((Q4:Q27>0)+(R4:R27>0)>0)*H4:H27)
    --

    HTH,

    RD
    =====================================================
    Please keep all correspondence within the Group, so all may benefit!
    =====================================================

    "Debgala" <Debgala@discussions.microsoft.com> wrote in message
    news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
    I wanted sumif to evaluate a range of cells over 2 columns.
    My formula looked like this
    =SUMIF(Q4:R27,">0",H4:H27)
    Oddly enough, it works if there is a value >0 in column Q,
    or in both columns Q and R,
    but will not work if there is a value >0 in column R but no value >0 in
    column Q.

    How can I make it work? (And if anyone has time, why doesn't it work the
    way it is now?)

    Thanks,
    Deb



  5. #5
    Debgala
    Guest

    Re: want sumif function's range to evaluate 2 columns

    Thanks Bob - it works like a charm.

    "Bob Phillips" wrote:

    > =SUMIF(Q4:Q27,">0",H4:H27)+SUMIF(R4:R27,">0",H4:H27)
    >
    > will work
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Debgala" <Debgala@discussions.microsoft.com> wrote in message
    > news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
    > > I wanted sumif to evaluate a range of cells over 2 columns.
    > > My formula looked like this
    > > =SUMIF(Q4:R27,">0",H4:H27)
    > > Oddly enough, it works if there is a value >0 in column Q,
    > > or in both columns Q and R,
    > > but will not work if there is a value >0 in column R but no value >0 in
    > > column Q.
    > >
    > > How can I make it work? (And if anyone has time, why doesn't it work the
    > > way it is now?)
    > >
    > > Thanks,
    > > Deb

    >
    >
    >


  6. #6
    Debgala
    Guest

    Re: want sumif function's range to evaluate 2 columns

    Thanks Domenic - your answer also works like a charm.

    "Domenic" wrote:

    > Assuming that you want to sum the values in Column H where the
    > corresponding values in Column Q or Column R are greater than zero,
    > try...
    >
    > =SUMPRODUCT(--((Q4:Q27>0)+(R4:R27>0)>0),H4:H27)
    >
    > Hope this helps!
    >
    > In article <CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com>,
    > "Debgala" <Debgala@discussions.microsoft.com> wrote:
    >
    > > I wanted sumif to evaluate a range of cells over 2 columns.
    > > My formula looked like this
    > > =SUMIF(Q4:R27,">0",H4:H27)
    > > Oddly enough, it works if there is a value >0 in column Q,
    > > or in both columns Q and R,
    > > but will not work if there is a value >0 in column R but no value >0 in
    > > column Q.
    > >
    > > How can I make it work? (And if anyone has time, why doesn't it work the
    > > way it is now?)
    > >
    > > Thanks,
    > > Deb

    >


  7. #7
    Bob Phillips
    Guest

    Re: want sumif function's range to evaluate 2 columns

    Really?

    Mine and Domenic's can't both work like a charm, as they will return
    different results. Mine is an AND condition, Domenic's is an OR. If say Q4
    and R4 are >0, mine will add H4 twice, Domenic's just once.

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Debgala" <Debgala@discussions.microsoft.com> wrote in message
    news:E2CB0136-5CEC-433F-914D-BEA519653A12@microsoft.com...
    > Thanks Bob - it works like a charm.
    >
    > "Bob Phillips" wrote:
    >
    > > =SUMIF(Q4:Q27,">0",H4:H27)+SUMIF(R4:R27,">0",H4:H27)
    > >
    > > will work
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Debgala" <Debgala@discussions.microsoft.com> wrote in message
    > > news:CBCD541C-C6F4-4071-BB49-ED4DAE790E69@microsoft.com...
    > > > I wanted sumif to evaluate a range of cells over 2 columns.
    > > > My formula looked like this
    > > > =SUMIF(Q4:R27,">0",H4:H27)
    > > > Oddly enough, it works if there is a value >0 in column Q,
    > > > or in both columns Q and R,
    > > > but will not work if there is a value >0 in column R but no value >0

    in
    > > > column Q.
    > > >
    > > > How can I make it work? (And if anyone has time, why doesn't it work

    the
    > > > way it is now?)
    > > >
    > > > Thanks,
    > > > Deb

    > >
    > >
    > >




  8. #8
    Ashish Mathur
    Guest

    RE: want sumif function's range to evaluate 2 columns

    Hi,

    It seems you want to sum the range if any one of columns (Q or R) have a 0.
    You may also try the following array formula (Ctrl+Shift+Enter)

    =sum(if((Q4:Q27>0)+(R4:R27>0),H4:H27))

    Regards,

    Ashish Mathur

    "Debgala" wrote:

    > I wanted sumif to evaluate a range of cells over 2 columns.
    > My formula looked like this
    > =SUMIF(Q4:R27,">0",H4:H27)
    > Oddly enough, it works if there is a value >0 in column Q,
    > or in both columns Q and R,
    > but will not work if there is a value >0 in column R but no value >0 in
    > column Q.
    >
    > How can I make it work? (And if anyone has time, why doesn't it work the
    > way it is now?)
    >
    > Thanks,
    > Deb


+ 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