URGENT: Please Advise. SumProduct and Operand Question

[Guest]

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,

 

[Guest]

=SUMPRODUCT((A1:A20>=2000)*(A1:A20<=2090)*(B1:B20))

 

[Guest]

Thanks for the prompt response Jonathan.

I pasted your formula into a cell, but is giving me a "#Value" error. What
else do I need to do? Please advise.

Thanks

 

[Bob Phillips]

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)

 

[Guest]

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

Thanks again,
Brent

 

[Ardus Petus]

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

HTH

 

[Guest]

Ardus,

Please explain more about what Ctrl+Shift+Enter does. And do I do this
before or after using a SumIf formula?

Thanks.

 

[Bob Phillips]

Ctrl-Shift-Enter is used instead of just Enter to signal to Excel that the
formula is an array formula. This is required when using an array of values,
or cells, in a function that normally expects a single value, or cell, such
as IF.

If you do it correctly, Excel will surround the formula in braces, {...}.
This is done by Excel, you do not need to. When you edit it, they disappear,
until you commit in the same way.

--
HTH

Bob Phillips

(replace xxxx in email address with googlemail if mailing direct)

 

[Guest]

Excellent. Thanks for the explanation and the help, guys. I really appreciate
it.

Brent