Index function

[JP Ronse]

Hi all,

Given this data (from the Excel help)

In range A1:C11

Fruit Price Count
Apples 0.69 40
Bananas 0.34 38
Lemons 0.55 15
Oranges 0.25 25
Pears 0.59 40

Almonds 2.80 10
Cashews 3.55 16
Peanuts 1.25 20
Walnuts 1.75 12


=SUM(INDEX(A1:C11;0;3;1)) returns 216 and the explanation is that this is
the sum of C1:C6.

For me, the sum of C1:C6 is 158!

What is there behind that I don't see?

Wkr,

JP

 

[Dave Peterson]

I'd say it was a typo in help.

I wonder if they meant:

=SUM(INDEX((A1:C6,A8:C11),0,3,1))

(I use commas as my list separator. You'll have to change them.)

 

[Lars-Åke Aspelin]

Hi all,

Given this data (from the Excel help)

In range A1:C11

Fruit Price Count
Apples 0.69 40
Bananas 0.34 38
Lemons 0.55 15
Oranges 0.25 25
Pears 0.59 40

Almonds 2.80 10
Cashews 3.55 16
Peanuts 1.25 20
Walnuts 1.75 12


=SUM(INDEX(A1:C11;0;3;1)) returns 216 and the explanation is that this is
the sum of C1:C6.

For me, the sum of C1:C6 is 158!

What is there behind that I don't see?

Wkr,

JP

The explanation is wrong.
The range A1:C11 is just one area. The fact that there are empty cells
within that area does not change this.
So the area_num = 1 in your formula does not add anything.

If you want the sum of C1:C6 by using areas try this formula:
=SUM(INDEX((A1:C6,A8:C11),0,3,1))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

The explanation is wrong.
The range A1:C11 is just one area. The fact that there are empty cells
within that area does not change this.
So the area_num = 1 in your formula does not add anything.

If you want the sum of C1:C6 by using areas try this formula:
=SUM(INDEX((A1:C6,A8:C11),0,3,1))

Hope this helps / Lars-Åke

or
=SUM(INDEX((A1:C6;A8:C11);0;3;1))
as you seem to have ; as list separator.

Lars-Åke

 

[JP Ronse]

Hi Dave & Lars-Åke,

Tnx for the reply. I dare not to ask if 'God' has made a mistake.

Wkr,

JP