+ Reply to Thread
Results 1 to 8 of 8

sumif when criteria is a range

  1. #1
    jeremy via OfficeKB.com
    Guest

    sumif when criteria is a range

    I'm still an excel newbie--I searched the archives but couldn't find the
    answer...

    How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).

    Thanks.


    --
    Message posted via http://www.officekb.com

  2. #2
    JE McGimpsey
    Guest

    Re: sumif when criteria is a range

    One way:

    =SUMPRODUCT(--(rng>=0),--(rng<10), rng)

    another:

    =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

    a third

    =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")



    In article <52E2906E1D7EC@OfficeKB.com>,
    "jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote:

    > I'm still an excel newbie--I searched the archives but couldn't find the
    > answer...
    >
    > How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
    >
    > Thanks.


  3. #3
    jeremy via OfficeKB.com
    Guest

    Re: sumif when criteria is a range

    Sorry, I forgot to specify that I want to sum a second column if the first
    meets the criteria range...

    Eg. =SUMIF($BE$9:$BE$272, 0=<X<10, ($BF$9:$BF$272))

    Of course, 0=<X<10 doesn't work.....

    Thanks....


    JE McGimpsey wrote:
    >One way:
    >
    > =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
    >
    >another:
    >
    > =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
    >
    >a third
    >
    > =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
    >
    >> I'm still an excel newbie--I searched the archives but couldn't find the
    >> answer...
    >>
    >> How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
    >>
    >> Thanks.



    --
    Message posted via http://www.officekb.com

  4. #4
    RagDyeR
    Guest

    Re: sumif when criteria is a range

    The Sumproduct formula that you quoted in your earlier post will do the same
    job for this by just adding an additional argument:

    =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)*$W$9:$W$272)

    OR

    =SUMIF($W$9:$W$272,">0")-SUMIF($W$9:$W$272,">=10")

    --

    HTH,

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

    "jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote in message
    news:52E2906E1D7EC@OfficeKB.com...
    I'm still an excel newbie--I searched the archives but couldn't find the
    answer...

    How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).

    Thanks.


    --
    Message posted via http://www.officekb.com



  5. #5
    RagDyeR
    Guest

    Re: sumif when criteria is a range

    John,

    Countif ?
    Confusing the OP's 2 posts?<g>
    --

    Regards,

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

    "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
    news:jemcgimpsey-EDD9F2.09404915082005@msnews.microsoft.com...
    One way:

    =SUMPRODUCT(--(rng>=0),--(rng<10), rng)

    another:

    =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")

    a third

    =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")



    In article <52E2906E1D7EC@OfficeKB.com>,
    "jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote:

    > I'm still an excel newbie--I searched the archives but couldn't find the
    > answer...
    >
    > How do I use sumif function when the criteria is a range (eg. >=0 to

    <10 ).
    >
    > Thanks.




  6. #6
    RagDyeR
    Guest

    Re: sumif when criteria is a range

    Again, you can use your original formula and just change the range that you
    wish to total:

    =SUMPRODUCT(($BE$9:$BE$272>=0)*($BE$9:$BE$272<10)*$BF$9:$BF$272)

    --

    HTH,

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


    "jeremy via OfficeKB.com" <forum@OfficeKB.com> wrote in message
    news:52E2C179B8580@OfficeKB.com...
    Sorry, I forgot to specify that I want to sum a second column if the first
    meets the criteria range...

    Eg. =SUMIF($BE$9:$BE$272, 0=<X<10, ($BF$9:$BF$272))

    Of course, 0=<X<10 doesn't work.....

    Thanks....


    JE McGimpsey wrote:
    >One way:
    >
    > =SUMPRODUCT(--(rng>=0),--(rng<10), rng)
    >
    >another:
    >
    > =COUNTIF(rng,">=0") - COUNTIF(rng, ">=10")
    >
    >a third
    >
    > =COUNTIF(rng, "<10") - COUNTIF(rng, "<0")
    >
    >> I'm still an excel newbie--I searched the archives but couldn't find the
    >> answer...
    >>
    >> How do I use sumif function when the criteria is a range (eg. >=0 to

    <10 ).
    >>
    >> Thanks.



    --
    Message posted via http://www.officekb.com



  7. #7
    JE McGimpsey
    Guest

    Re: sumif when criteria is a range

    In article <#iXsoIboFHA.1048@tk2msftngp13.phx.gbl>,
    "RagDyeR" <ragdyer@cutoutmsn.com> wrote:

    > Countif ?


    Yup - should have been SUMIF()

  8. #8
    jeremy via OfficeKB.com
    Guest

    Re: sumif when criteria is a range

    thanks--that was brillant...

    RagDyeR wrote:
    >The Sumproduct formula that you quoted in your earlier post will do the same
    >job for this by just adding an additional argument:
    >
    >=SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)*$W$9:$W$272)
    >
    >OR
    >
    >=SUMIF($W$9:$W$272,">0")-SUMIF($W$9:$W$272,">=10")
    >
    >I'm still an excel newbie--I searched the archives but couldn't find the
    >answer...
    >
    >How do I use sumif function when the criteria is a range (eg. >=0 to <10 ).
    >
    >Thanks.



    --
    Message posted via http://www.officekb.com

+ 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