Great. That worked well. Thanks to you both for your good help; have a great
week.

Thanks again,
Brent

"Bob Phillips" wrote:

> FYI, you can test a number of particular values like so
>
> =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A20,{2000,2010,2020,2030,2040},0))),B2:B20)
>
> --
> HTH
>
> Bob Phillips
>
> (replace xxxx in email address with googlemail if mailing direct)
>
> "Brent E" <BrentE@discussions.microsoft.com> wrote in message
> news:2582569A-C921-4D9F-B9D6-C7AACA43B53B@microsoft.com...
> > Good morning Everybody,
> >
> > My CFO asked me to see if I could find a way to save us a lot of time and
> > work by finding an excel formula that will do the following. I would
> greatly
> > appreciate any assistance.
> >
> > Suppose you have sample values like this:
> > A B
> > 2000 (20,000)
> > 2040 -
> > 9000 (29,000)
> > 2010 100,000
> > 2030 -
> > 9010 20,000
> > 9020 -
> > 9040 400000
> >
> > We would like to use a formula that can do this:
> >
> > Search Column A from A1 to A20 for a range of values (e.g. 2000 to 2090),
> > For all values within this range, sum their Column B Components in a
> single
> > cell somewhere on the spreadsheet, say N2. So in this example, two values
> are
> > null so (20,000) and 100,000 would be summed in N2.
> >
> > I tried using SumProduct. I can get SumProduct to work if I use only one
> > test range:
> > =SUMPRODUCT(--(A1:A8=2000),(B1:B8))
> > But this only tests for one of the values in the range between 2000 and
> 2090
> > and
> > Returns a value of -20,000 (which is correct but excludes the rest of the
> > range).
> >
> > I also tried using an operator, but am not certain of the proper syntax. I
> > experimented w/ these:
> >
> >
> =SUMPRODUCT(--(A1:A8=2000),--(A1:A8=2010),--(A1:A8=2030),--(A1:A8=2040),B1:B
> 8)
> > Returns a 0 value
> >
> >
> =SUMPRODUCT(OR(--(A1:A8=2000),--(A1:A8=2010),--A1:A8=2030),--A1:A8=2040),B1:
> B8)
> > Doesn't work, generates error message
> >
> > =SUMPRODUCT(OR(A1:A8=2000,A1:A8=2010,A1:A8=2030,A1:A8=2040),B1:B8)
> > Generates "#Value"
> >
> > =SUMPRODUCT(--(A1:A8=OR(2000,2010,2030,2040)),B1:B8)
> > Returns a 0 value
> >
> > Please advise.
> >
> > Cordially,
> >
>
>
>