Sumif and a range

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Feb 10, 2006.

  1. Guest

    Guest Guest

    Hello Wizards,

    Col a has 200 - 300 entries limited to the integers 1 - 20 . Col b has
    values associated with col a numbers. What do I use to get the sum of the
    values in col b that are associated with a's digits 1-5, 6-10, etc.

    TIA for your help!
    Sam
     
    Guest, Feb 10, 2006
    #1
    1. Advertisements

  2. Guest

    Bob Phillips Guest

    =SUMIF(A:A,"<=5",B:B)

    =SUMIF(A:A,"<=10",B:B)-SUMIF(A:A,"<=5",B:B)

    etc.

    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Metolius Dad" <> wrote in message
    news:...
    > Hello Wizards,
    >
    > Col a has 200 - 300 entries limited to the integers 1 - 20 . Col b has
    > values associated with col a numbers. What do I use to get the sum of the
    > values in col b that are associated with a's digits 1-5, 6-10, etc.
    >
    > TIA for your help!
    > Sam
     
    Bob Phillips, Feb 10, 2006
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    This works well, however, can I push a bit more and ask how I can do this
    same task but being able to use the portion in quotes as a reference. As I
    experimented =SUMIF(A:A,<=D10,B:B)-SUMIF(A:A,<=D9,B:B) with col D being the
    limits of each range didnt work.

    Again, thanks for any assistance.
    Sam


    "Bob Phillips" wrote:

    > =SUMIF(A:A,"<=5",B:B)
    >
    > =SUMIF(A:A,"<=10",B:B)-SUMIF(A:A,"<=5",B:B)
    >
    > etc.
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (remove nothere from email address if mailing direct)
    >
    > "Metolius Dad" <> wrote in message
    > news:...
    > > Hello Wizards,
    > >
    > > Col a has 200 - 300 entries limited to the integers 1 - 20 . Col b has
    > > values associated with col a numbers. What do I use to get the sum of the
    > > values in col b that are associated with a's digits 1-5, 6-10, etc.
    > >
    > > TIA for your help!
    > > Sam

    >
    >
    >
     
    Guest, Feb 13, 2006
    #3
  4. Guest

    Bob Phillips Guest

    You need

    =SUMIF(A:A,"<="&D10,B:B)-SUMIF(A:A,"<="&D9,B:B)


    --
    HTH

    Bob Phillips

    (remove nothere from email address if mailing direct)

    "Metolius Dad" <> wrote in message
    news:...
    > This works well, however, can I push a bit more and ask how I can do this
    > same task but being able to use the portion in quotes as a reference. As

    I
    > experimented =SUMIF(A:A,<=D10,B:B)-SUMIF(A:A,<=D9,B:B) with col D being

    the
    > limits of each range didnt work.
    >
    > Again, thanks for any assistance.
    > Sam
    >
    >
    > "Bob Phillips" wrote:
    >
    > > =SUMIF(A:A,"<=5",B:B)
    > >
    > > =SUMIF(A:A,"<=10",B:B)-SUMIF(A:A,"<=5",B:B)
    > >
    > > etc.
    > >
    > > --
    > > HTH
    > >
    > > Bob Phillips
    > >
    > > (remove nothere from email address if mailing direct)
    > >
    > > "Metolius Dad" <> wrote in message
    > > news:...
    > > > Hello Wizards,
    > > >
    > > > Col a has 200 - 300 entries limited to the integers 1 - 20 . Col b has
    > > > values associated with col a numbers. What do I use to get the sum of

    the
    > > > values in col b that are associated with a's digits 1-5, 6-10, etc.
    > > >
    > > > TIA for your help!
    > > > Sam

    > >
    > >
    > >
     
    Bob Phillips, Feb 14, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Oscar
    Replies:
    2
    Views:
    822
    Bob Phillips
    Jan 11, 2005
  2. Guest

    nested sumif or sumif with two criteria

    Guest, Apr 4, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    1,334
    Harlan Grove
    Apr 5, 2005
  3. Guest

    sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A"

    Guest, Jun 12, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    9
    Views:
    606
    Bob Phillips
    Jun 12, 2007
  4. markholt

    SumIf - when I fill down the Range, Criteria & sum range changes

    markholt, Oct 27, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    302
    Sheeloo
    Oct 28, 2008
  5. PCLIVE
    Replies:
    3
    Views:
    864
    Lars-Åke Aspelin
    Jul 15, 2009
Loading...

Share This Page