=SUM(IF(A1:A8>=2000,IF(A1:A8<=2090,B1:B8)))
enter as array formula (Ctrl+Shift+Enter)

HTH
--
AP

"Brent E" <BrentE@discussions.microsoft.com> a crit dans le message de
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:B8)
> 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,
>