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
|