Thread: Data ranges within a column

Range D15 through D830 contains numbers of various numeric values. In column
F15 I want to report the total number of entries in D15 through D830 that
fall within certain parameters, ie...all of the numbers that fall between 1
and 999. In F15 I want to report all ov the numbers that fall between 1000
and 2499, etc....

Is this a VLOOKUP function and how do I arrange it to properly report?

Thanks!

Jack

Try this:

F15: =SUMPRODUCT((D15:D830>=1)*(D15:D830<=999))
F16: =SUMPRODUCT((D15:D830>=1000)*(D15:D830<=2499))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JT" wrote:

> Range D15 through D830 contains numbers of various numeric values. In column
> F15 I want to report the total number of entries in D15 through D830 that
> fall within certain parameters, ie...all of the numbers that fall between 1
> and 999. In F15 I want to report all ov the numbers that fall between 1000
> and 2499, etc....
>
> Is this a VLOOKUP function and how do I arrange it to properly report?
>
> Thanks!
>
> Jack

Ron:

It worked just fine! Many thanks!

I did come up with one more question on a different subject, though.

In d15 I have a numeric value that indicates a donated dollar figure that
will be paid out over either 3, 4 or 5 years. Column c15 indicates the
number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar
figure that will be paid in that year. For example: if the donor donates
1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show
the amount due that year...with a "0" in I15. Same for a 3 year payout or a
5 year payout.

Can you help me again?

Jack

If the

"Ron Coderre" wrote:

> Try this:
>
> F15: =SUMPRODUCT((D15:D830>=1)*(D15:D830<=999))
> F16: =SUMPRODUCT((D15:D830>=1000)*(D15:D830<=2499))
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "JT" wrote:
>
> > Range D15 through D830 contains numbers of various numeric values. In column
> > F15 I want to report the total number of entries in D15 through D830 that
> > fall within certain parameters, ie...all of the numbers that fall between 1
> > and 999. In F15 I want to report all ov the numbers that fall between 1000
> > and 2499, etc....
> >
> > Is this a VLOOKUP function and how do I arrange it to properly report?
> >
> > Thanks!
> >
> > Jack

Try this:

E15:
=IF($C15>COLUMNS($D$1:D$1),TRUNC($D15/$C15),IF($C15=COLUMNS($D$1:D$1),$D15-TRUNC(($C15-1)*$D15/$C15),""))

Copy that formula across thru I15 and down as far as you need.

Note: that formula rounds down in the first months of the allocation and
compensates in the last month.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"JT" wrote:

> Ron:
>
> It worked just fine! Many thanks!
>
> I did come up with one more question on a different subject, though.
>
> In d15 I have a numeric value that indicates a donated dollar figure that
> will be paid out over either 3, 4 or 5 years. Column c15 indicates the
> number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar
> figure that will be paid in that year. For example: if the donor donates
> 1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show
> the amount due that year...with a "0" in I15. Same for a 3 year payout or a
> 5 year payout.
>
> Can you help me again?
>
> Jack
>
> If the
>
> "Ron Coderre" wrote:
>
> > Try this:
> >
> > F15: =SUMPRODUCT((D15:D830>=1)*(D15:D830<=999))
> > F16: =SUMPRODUCT((D15:D830>=1000)*(D15:D830<=2499))
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "JT" wrote:
> >
> > > Range D15 through D830 contains numbers of various numeric values. In column
> > > F15 I want to report the total number of entries in D15 through D830 that
> > > fall within certain parameters, ie...all of the numbers that fall between 1
> > > and 999. In F15 I want to report all ov the numbers that fall between 1000
> > > and 2499, etc....
> > >
> > > Is this a VLOOKUP function and how do I arrange it to properly report?
> > >
> > > Thanks!
> > >
> > > Jack

Very helpful, Ron! Thanks again!

JT

"Ron Coderre" wrote:

> Try this:
>
> E15:
> =IF($C15>COLUMNS($D$1:D$1),TRUNC($D15/$C15),IF($C15=COLUMNS($D$1:D$1),$D15-TRUNC(($C15-1)*$D15/$C15),""))
>
> Copy that formula across thru I15 and down as far as you need.
>
> Note: that formula rounds down in the first months of the allocation and
> compensates in the last month.
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "JT" wrote:
>
> > Ron:
> >
> > It worked just fine! Many thanks!
> >
> > I did come up with one more question on a different subject, though.
> >
> > In d15 I have a numeric value that indicates a donated dollar figure that
> > will be paid out over either 3, 4 or 5 years. Column c15 indicates the
> > number of years.I n e15, F15, G15, H15 and I15 I want to show the dollar
> > figure that will be paid in that year. For example: if the donor donates
> > 1000 and chooses a payout of 4 years, I want e15, f15, g15 and H15 to show
> > the amount due that year...with a "0" in I15. Same for a 3 year payout or a
> > 5 year payout.
> >
> > Can you help me again?
> >
> > Jack
> >
> > If the
> >
> > "Ron Coderre" wrote:
> >
> > > Try this:
> > >
> > > F15: =SUMPRODUCT((D15:D830>=1)*(D15:D830<=999))
> > > F16: =SUMPRODUCT((D15:D830>=1000)*(D15:D830<=2499))
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "JT" wrote:
> > >
> > > > Range D15 through D830 contains numbers of various numeric values. In column
> > > > F15 I want to report the total number of entries in D15 through D830 that
> > > > fall within certain parameters, ie...all of the numbers that fall between 1
> > > > and 999. In F15 I want to report all ov the numbers that fall between 1000
> > > > and 2499, etc....
> > > >
> > > > Is this a VLOOKUP function and how do I arrange it to properly report?
> > > >
> > > > Thanks!
> > > >
> > > > Jack