Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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

Array formula on INDEX function not working
 

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