You can sum a range of values within a table using the INDEX function in Excel.  This is valuable when you want to extract key metrics from a table and put them in an Excel Dashboard.

To make this work you firstly need to start your Excel formula with the SUM function followed by the INDEX function.

So it will look something like this:  =SUM(INDEX(Array, Row_Num, Column_Num))

The Array will be your table of data, the Row_Num will be blank and the Column_Num will be the column number where you want to SUM the values.

When we dissect the formula (by highlighting the INDEX function and pressing F9) we can see that the following is happening: =SUM({8959;7840;7507;6690;5802;5487;3949;3836;3587;3210})

So in effect we are summing the array of values within the table.  See the example below that shows you how this is done.

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: We need to place first the INDEX function inside the SUM function.

=SUM(INDEX

 

index-sum-01

The Index arguments:

array

What is the range / table?

Reference the range of cells here that we want to get the values from:

=SUM(INDEX($C$12:$E$21,

 

index-sum-02

row_num

What is the row number we want to return?

We do not need to return the row, as we want to just sum all of the sales. Leave the row number blank:

=SUM(INDEX($C$12:$E$21,,

index-sum-03

column_num

What is the column number we want to return?

Since we want the sales column, this is column number 2. So place in 2:

=SUM(INDEX($C$12:$E$21,,2))

index-sum-04

Now you are able to get the Total Sales:

index-sum-05

 

STEP 2: Now let us try how we can use this with the AVERAGE function. We need to place first the INDEX function inside the AVERAGE function.

=AVERAGE(INDEX

index-sum-06

The Index arguments:

array

What is the range / table?

Reference the range of cells here that we want to get the values from:

=AVERAGE(INDEX($C$12:$E$21,

index-sum-07

row_num

What is the row number we want to return?

We do not need to return the row, as we want to just average all of the units. Leave the row number blank:

=AVERAGE(INDEX($C$12:$E$21,,

index-sum-08

column_num

What is the column number we want to return?

Since we want the units column, this is column number 3. So place in 3:

=AVERAGE(INDEX($C$12:$E$21,,3))

index-sum-09

Now you are able to get the Average Units Sold:

index-sum-10

 

Index - Sum

HELPFUL RESOURCE:

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Excel´s TEXT Function The TEXT function in Excel allows you to convert a numeric value to a specific format by using special format strings. If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the TEXT function is your sa...
Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria. This is a little advanced so you will need to drop what you are doing and really focus.  Let's go... First we need to convert our data into an Excel...
SUMIF Function: One Criteria & Sum Range The SUMIF function can also be used to apply the criteria to one range and sum the corresponding values in a different range. So if you have a list of Sales Reps in one list and their Sales in another list and want to Sum the sales of only one of the Sales Reps, then the SUMIF...
VLOOKUP with Multiple Criteria in Excel ‘VLOOKUP’ is now so common that everyone who has ever been working with Excel has heard of it. Not necessarily because they know how to use it, but because some savvy Excel-user always talks about it at the office. Advanced users have seen the use of adding more than one...