Fusing Together The SUM and Index Function | Excel | Tutorial

The purpose of the index function is to help us find the position of our data in Excel so that we can retrieve it and use it for various purposes. In this example, we will be using the Index function to find the position of our monthly sales figures from table 1 so that we can summarise it into quarterly figures.

In this tutorial, I am going to show you how to use the Index function.

Video Guide

Instructions

Create a table which contains monthly sales figures for three financial years. In addition, have another table ready which summarises the monthly figures into 4 quarters (Q1, Q2, Q3 & Q4).

Firstly, we need to summarise the Q1 sales figures for 2015 into cell C13 so we will type the

Formula =SUM(INDEX(array,row_num,column_num))

Array – This is asking us where the Q1 data is stored, for this, we can highlight all of the Q1 data. Once selected we need to make the cell reference absolute as we will be dragging the formulae down later. We can then type a comma to close the current function.

Row_num – This is asking us which row the data we need is located. As the data (2015) we need is in row 1 we can type “1”. We can then type a comma to close the current function and move on to the next.

Column_num – This asking us which column the data we need is located. As we need all three columns we can type “0” and close brackets twice, once for the function sum and once for the function index and then click enter. This should now give us the total value for Q1.

We now need to do the same for Q2, Q3, and Q4. For this, I am going to drag the formula across.

This will only show us the data for the sum of Q1 as we have used absolute values so we can now double-click in cell D13, this will show us that the data in Q1 is selected, we can drag the selection to the Q2 data to quickly change the formula. We need to then do the same for Q3 and Q4

Once this is done we can drag down the formula to 2016 and 2017.  This is now only showing us the data for 2015. This is because the row that the index formula is looking at is still row 1 which is for 2015. We need to change the row from 1 to 2 for 2016 and 1 to 3 for 2017 data.

Note: The row number depends on what data you have selected. If the first row you selected was row 5. This would mean that row 5 is row 1 of the highlighted.

In this example, row 5 was the first row that was highlighted therefore this would be row1. Row 1 is also where the 2015 data is located which is why we used row 1 for 2015 figures.