=SUM(B2:INDEX(A2:C6,5,2)) sums the range starting at B2 and ends at the intersection of the fifth row and the second column of the range A2:A6 : INDEX « Lookup Reference functions « Microsoft Office Excel 2007 Tutorial

Home
Microsoft Office Excel 2007 Tutorial
1.Introduction
2.Editing
3.Format Style
4.Table
5.Chart
6.Formula
7.Workbook Worksheet
8.Wordart Clip Art Shape Picture
9.PivotTable PivotChart
10.Data Analysis
11.Macro ActiveX Add in
12.Security
13.Collaboration
14.Database functions
15.Date Time functions
16.Engineering functions
17.Information functions
18.Logical functions
19.Lookup Reference functions
20.Math Trigonometry functions
21.Statistical functions
22.Text functions
Microsoft Office Excel 2007 Tutorial » Lookup Reference functions » INDEX 




Input the formula: =SUM(B2:INDEX(A2:C6,5,2)). Press Enter to get the result.
Input the formula: =SUM(B2:INDEX(A2:C6,5,2))














19.8.INDEX
19.8.1.INDEX Uses an index to choose a value from a reference or arrayINDEX Uses an index to choose a value from a reference or array
19.8.2.=INDEX(B1:C4,2,1) gets the value at the intersection of the second row and first column in the range=INDEX(B1:C4,2,1) gets the value at the intersection of the second row and first column in the range
19.8.3.=INDEX({1,2;3,4},0,2) returns the value in the first row, second column in the array constant=INDEX({1,2;3,4},0,2) returns the value in the first row, second column in the array constant
19.8.4.=INDEX(A2:C6,2,3) gets the intersection of the second row and third column in the range A2:C6=INDEX(A2:C6,2,3) gets the intersection of the second row and third column in the range A2:C6
19.8.5.=INDEX((A1:C6,A8:C11),2,2,2) gets the intersection of the second row and second column in the second area of A8:C11=INDEX((A1:C6,A8:C11),2,2,2) gets the intersection of the second row and second column in the second area of A8:C11
19.8.6.=SUM(INDEX(A1:C11,0,3,1)) returns the sum of the third column in the first area of the range A1:C11=SUM(INDEX(A1:C11,0,3,1)) returns the sum of the third column in the first area of the range A1:C11
19.8.7.=SUM(B2:INDEX(A2:C6,5,2)) sums the range starting at B2 and ends at the intersection of the fifth row and the second column of the range A2:A6=SUM(B2:INDEX(A2:C6,5,2)) sums the range starting at B2 and ends at the intersection of the fifth row and the second column of the range A2:A6
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.