Sumif and a range

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

1. GuestGuest

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.

Sam

Guest, Feb 10, 2006

2. Bob PhillipsGuest

=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.
>
> Sam

Bob Phillips, Feb 10, 2006

3. GuestGuest

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
4. Bob PhillipsGuest

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