Sum with INDEX()

[Zaigham]

Being a novice I am unable to understand the logic of below mentioned example (found at ExcelisFun)that how the formula calculates the value of sales with the help of INDEX(), I have tried to break the formula in parts but the result of =INDEX(B5:B18,MATCH(D5,A5:A18,0)) comes to "2" which is the 2nd value or reference of the SUM(). But when I enter =SUM(B5:2) it gives error message and do not allow to enter the function.

Excel Workbook
	A	B	C	D	E	F	G	H
4	Day	Sales	*	Select final day for summing purposes	Sum Through Day10	*	*	*
5	Day1	$1.00	*	Day10	$17.00	*	*	*
6	Day2	$2.00	*	*	$17.00	*	*	*
7	Day3	$1.00	*	*	*	*	*	*
8	Day4	$2.00	*	*	*	*	*	*
9	Day5	$2.00	*	*	*	*	*	*
10	Day6	$2.00	*	*	*	*	*	*
11	Day7	$2.00	*	10	MATCH(D5,A5:A18,0)	*	*	*
12	Day8	$1.00	*	2	INDEX(B5:B18,MATCH(D5,A5:A18,0))	*	*	*
13	Day9	$2.00	*	17.00	SUM(B5:INDEX(B5:B18,MATCH(D5,A5:A18,0)))	*	*	*
14	Day10	$2.00	*	*	*	*	*	*
15	Day11	$1.00	*	*	SUM(B5:2) gives Error message	*	*	*
16	Day12	$2.00	*	*	*	*	*	*
17	Day13	$1.00	*	*	*	*	*	*
18	Day14	$1.00	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*
I(12)

I hope that somebody will spare his valuable time for me.

 

[T. Valko]

Being a novice I am unable to understand the logic of below mentioned example (found at ExcelisFun)that how the formula calculates the value of sales with the help of INDEX(), I have tried to break the formula in parts but the result of =INDEX(B5:B18,MATCH(D5,A5:A18,0)) comes to "2" which is the 2nd value or reference of the SUM(). But when I enter =SUM(B5:2) it gives error message and do not allow to enter the function.

Excel Workbook
	A	B	C	D	E	F	G	H
4	Day	Sales	*	Select final day for summing purposes	Sum Through Day10	*	*	*
5	Day1	$1.00	*	Day10	$17.00	*	*	*
6	Day2	$2.00	*	*	$17.00	*	*	*
7	Day3	$1.00	*	*	*	*	*	*
8	Day4	$2.00	*	*	*	*	*	*
9	Day5	$2.00	*	*	*	*	*	*
10	Day6	$2.00	*	*	*	*	*	*
11	Day7	$2.00	*	10	MATCH(D5,A5:A18,0)	*	*	*
12	Day8	$1.00	*	2	INDEX(B5:B18,MATCH(D5,A5:A18,0))	*	*	*
13	Day9	$2.00	*	17.00	SUM(B5:INDEX(B5:B18,MATCH(D5,A5:A18,0)))	*	*	*
14	Day10	$2.00	*	*	*	*	*	*
15	Day11	$1.00	*	*	SUM(B5:2) gives Error message	*	*	*
16	Day12	$2.00	*	*	*	*	*	*
17	Day13	$1.00	*	*	*	*	*	*
18	Day14	$1.00	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*
I(12)

I hope that somebody will spare his valuable time for me.

In this application the INDEX(...) function returns a REFERENCE.

=SUM(B5:INDEX(B5:B18,MATCH(D5,A5:A18,0)))

MATCH = 10

INDEX(B5:B18,10) = B14

So:

=SUM(B5:B14)

 

[Zaigham]

Hello Biff

Thank you very much for explanation. Actually I am unable to differentiate between "array form" and "reference form" of INDEX() so it is difficult for me to recognize the form of INDEX(). May be more practice, help me to overcome this problem.

Zaigham

 

[T. Valko]

Hello Biff

Thank you very much for explanation. Actually I am unable to differentiate between "array form" and "reference form" of INDEX() so it is difficult for me to recognize the form of INDEX(). May be more practice, help me to overcome this problem.

Zaigham

Practice makes perfect!

Some day it will just "click" and then you realize that most of this stuff is fairly easy and makes perfect sense.

Thanks for the feedback!

 

[Zaigham]

Thanks for guidance and encouragement. So nice of you.