........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

Because B1:B2 just returns the first value in that array, 2.

Try this

=SUM(INDEX(IF(ISNUMBER(MATCH(ROW(A1:A5),B1:B2,0)), A1:A5),0))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"vsoler" wrote in message
oups.com...

.......A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

Hi,

The answer depends on the version, if you are using 2003 or less then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire


"vsoler" wrote:


........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

Hi

You could use the non-array entered formula
=SUM(INDEX(A1:A5,B1),INDEX(A1:A5,B2))

--
Regards

Roger Govier


"vsoler" wrote in message
oups.com...

.......A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

Or use:
=SUM(SUM(OFFSET(A1,B1:B2-1,)))
(still array entered)



ShaneDevenshire wrote:

Hi,

The answer depends on the version, if you are using 2003 or less then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire

"vsoler" wrote:


........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you



--

Dave Peterson

On 3 jun, 14:19, Dave Peterson wrote:
Or use:
=SUM(SUM(OFFSET(A1,B1:B2-1,)))
(still array entered)





ShaneDevenshire wrote:

Hi,

The answer depends on the version, if you are using 2003 or less then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire

"vsoler" wrote:

........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

--

Dave Peterson- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thank you all.

Bob's and Dave's formulas are perfect for me. Shane's, except for the
-1 was perfect as well.
Rogers' also works, but sometimes I can have more than 2 values to
add, and his formula would not adjust automatically.

I try to avoid the OFFSET function, whenever possible, because it is a
volatile function. But it works perfectly.

However, I am still wondering why the INDEX function does not work
when used with Ctrl-Shift-Enter keys.

Thank you again

Hi
I agree, my non-array entered solution would have to add each term
require to the formula.
I also agree with avoiding volatile functions.

But Bob's solution works, and does not require any volatile functions.

--
Regards

Roger Govier


"vsoler" wrote in message
oups.com...
On 3 jun, 14:19, Dave Peterson wrote:
Or use:
=SUM(SUM(OFFSET(A1,B1:B2-1,)))
(still array entered)





ShaneDevenshire wrote:

Hi,

The answer depends on the version, if you are using 2003 or less
then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire

"vsoler" wrote:

........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

--

Dave Peterson- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thank you all.

Bob's and Dave's formulas are perfect for me. Shane's, except for the
-1 was perfect as well.
Rogers' also works, but sometimes I can have more than 2 values to
add, and his formula would not adjust automatically.

I try to avoid the OFFSET function, whenever possible, because it is a
volatile function. But it works perfectly.

However, I am still wondering why the INDEX function does not work
when used with Ctrl-Shift-Enter keys.

Thank you again

When I use tools|evaluate formula, excel changes that row argument to a
number--not an array.

vsoler wrote:

On 3 jun, 14:19, Dave Peterson wrote:
Or use:
=SUM(SUM(OFFSET(A1,B1:B2-1,)))
(still array entered)





ShaneDevenshire wrote:

Hi,

The answer depends on the version, if you are using 2003 or less then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire

"vsoler" wrote:

........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

--

Dave Peterson- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thank you all.

Bob's and Dave's formulas are perfect for me. Shane's, except for the
-1 was perfect as well.
Rogers' also works, but sometimes I can have more than 2 values to
add, and his formula would not adjust automatically.

I try to avoid the OFFSET function, whenever possible, because it is a
volatile function. But it works perfectly.

However, I am still wondering why the INDEX function does not work
when used with Ctrl-Shift-Enter keys.

Thank you again

--

Dave Peterson

Indeed, it was written to specifically avoid volatile functions, and to be
extendible.

Boy I'm good! <vbseg

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roger Govier" wrote in message
...
Hi
I agree, my non-array entered solution would have to add each term require
to the formula.
I also agree with avoiding volatile functions.

But Bob's solution works, and does not require any volatile functions.

--
Regards

Roger Govier


"vsoler" wrote in message
oups.com...
On 3 jun, 14:19, Dave Peterson wrote:
Or use:
=SUM(SUM(OFFSET(A1,B1:B2-1,)))
(still array entered)





ShaneDevenshire wrote:

Hi,

The answer depends on the version, if you are using 2003 or less then

change the entries in column B to read 1,3 rather than 2,4.

=SUM(SUM(OFFSET(A1,B1:B2,)))

Enter this formula as an array.
--
Cheers,
Shane Devenshire

"vsoler" wrote:

........A.........B
1.....1..........2
2.....3..........4
3.....7
4.....12
5.....13

To my surprise, {=SUM(INDEX(A1:A5;B1:B2))} returns 3, and not 15
(entered with Ctrl-Shift-Enter)

Can anybody explain and give correct formula?

Thank you

--

Dave Peterson- Ocultar texto de la cita -

- Mostrar texto de la cita -

Thank you all.

Bob's and Dave's formulas are perfect for me. Shane's, except for the
-1 was perfect as well.
Rogers' also works, but sometimes I can have more than 2 values to
add, and his formula would not adjust automatically.

I try to avoid the OFFSET function, whenever possible, because it is a
volatile function. But it works perfectly.

However, I am still wondering why the INDEX function does not work
when used with Ctrl-Shift-Enter keys.

Thank you again