URGENT: Please Advise. SumProduct and Operand Question

[Brent E]

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,

[Jonathan Cooper]

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

"Brent E" wrote:

> 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,
>

[Brent E]

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

"Jonathan Cooper" wrote:

> =SUMPRODUCT((A1:A20>=2000)*(A1:A20<=2090)*(B1:B20))
>
> "Brent E" wrote:
>
> > 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,
> >

[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)

"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,
>

[Brent E]

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,
> >
>
>
>

[Ardus Petus]

=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,
>

[Brent E]

Ardus,

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

Thanks.

"Ardus Petus" wrote:

> =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,
> >
>
>
>

[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)

"Brent E" <BrentE@discussions.microsoft.com> wrote in message
news:9A72F970-AF05-4F58-A1CE-7AC7E9418C77@microsoft.com...
> Ardus,
>
> Please explain more about what Ctrl+Shift+Enter does. And do I do this
> before or after using a SumIf formula?
>
> Thanks.
>
> "Ardus Petus" wrote:
>
> > =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: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,
> > >
> >
> >
> >

[Brent E]

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

Brent

"Bob Phillips" wrote:

> 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)
>
> "Brent E" <BrentE@discussions.microsoft.com> wrote in message
> news:9A72F970-AF05-4F58-A1CE-7AC7E9418C77@microsoft.com...
> > Ardus,
> >
> > Please explain more about what Ctrl+Shift+Enter does. And do I do this
> > before or after using a SumIf formula?
> >
> > Thanks.
> >
> > "Ardus Petus" wrote:
> >
> > > =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: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,
> > > >
> > >
> > >
> > >
>
>
>