Index function
 

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

Index function
 

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



JP Ronse wrote:

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

Index function
 

On Sun, 30 Aug 2009 16:32:04 +0200, "JP Ronse"
wrote:

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

Index function
 

On Sun, 30 Aug 2009 14:48:30 GMT, Lars-Åke Aspelin
wrote:

On Sun, 30 Aug 2009 16:32:04 +0200, "JP Ronse"
wrote:

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


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

Lars-Åke

Index function
 

Hi Dave & Lars-Åke,

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

Wkr,

JP





"Lars-Åke Aspelin" wrote in message
...
On Sun, 30 Aug 2009 16:32:04 +0200, "JP Ronse"
wrote:

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