Sum range with INDEX
Sum range with INDEX is very helpful in excel, especially in cases where the values in rows and columns are flexible and subject to change based on who is using at a particular point. This function then helps to ensure that the sum is always valid for every time that the rows or columns are changed by a user.
“data” here refers to a certain range in the spreadsheet.
In Excel, the INDEX function performs a lookup of each value by taking consideration of the position of such value.
How the INDEX function works
The INDEX function carries out its operation on rows and columns in a unique way. For instance, if the argument provided for the number in a row is zero or null, what the INDEX function does is to return all the values inside the column as already supplied by the argument for each column number.
In the same vein, if the argument supplied for the number in a column is zero or null. The INDEX function returns all the values in the row as already supplied by the argument for each row number too.
Let us assume that we are to find the sum of the tuition fees for five universities from the USA and UK using the INDEX function. This is the tuition for three academic sessions.
Please note that the figures depicted in the image do not represent the exact tuition paid in the schools mentioned.
Figure 1: Find the sum with the index function.
Now, to find the sum of the tuition students pays at the end of three academic sessions, we use the formula:
Where 1 refers to all the values in the first row.
Remember that in the generic formula,
=SUM (INDEX (data, 0, H5) ).
“data” here represents all the values in the named range, B3:D7.
The INDEX function does a sum of the tuition fee for each year at Oxford.
Figure 2: Using the SUM range with INDEX function.
To get the sum of tuition of three years for each college, simply do a drag-down of the “Total” cell, which immediately populates each row.
Figure 3: The sum range with INDEX function for each row.
We’re still going to use the same scenario in example 1, only that this time, we would consider getting the SUM of values in the column, rather than rows.
Because it is a column function, the formula for doing this is slightly different. Here the arrangement of the “H5” element is different. It comes after our zero.
Applying this formula to the cell returns the sum of all the Year 1 tuition fees for the different colleges.
Figure 4: SUM range with INDEX for columns.
To get the sum of the remaining columns, simply apply the formula to the other cells, switching up the column number for each formula.
Figure 5: SUM range with INDEX for all rows and columns.
At the end of this, we would have got the SUM of all the values using the INDEX function. What this implies afterward is that the SUMs remain the same every time the tuition changes for any college. It can also be streamlined to just one row or one column at a time.