How to Sum/Return an Array with the Index Function

We can use the INDEX function to return an ARRAY of values to another function like the SUM function by integrating INDEX with the IF and N functions. We can use this to SUM values that are present in specific cells. This article will walk through the process.

Figure 1: How to Sum/ Return an Array with the Index Function

Formula to Sum/ Return an Array with the Index Function

=SUM(INDEX(B4:B11,N(IF(1,{1,6,8}))))

Setting up the Data

• The names of salespersons have been placed in Column A as shown in figure 2
• The commission of the salespersons are in Column B for certain sold items
• Column C contains the cell numbers for each salesperson’s commission based on the range: A4:A11. We intend to SUM the commissions of each salesperson by using Column C.

Figure 2: Setting up the Data

Sum/ Return an Array with the Index Function

• We will click on Cell D4
• We will input the formula into Cell D4: =SUM(INDEX(B4:B11,N(IF(1,{1,6,8}))))
• We will press the enter key to get the result

Figure 3: Sum of Jake’s Commission

• We will use the fill handle to drag down and get the result for the other two cells

Figure 4: Sum of the Salespeople’s Commission with the SUM Function

Explanation

The IF function returns as TRUE any number that is contained in the array ({1,6,8}) after the logical test (1) is true (the logical statement here means that any value that is a digit should be returned as TRUE).

The N function returns the values that are returned as TRUE by the IF function to the INDEX function. The INDEX function uses these values to check through the RANGE OF COMMISION (B4:B11) for their positions within COLUMN B. The INDEX FUNCTION then reads the digits in the format below:

• 1 is returned or read as 50
• 6 is returned or read as 55
• 8 is returned or read as 27

The INDEX FUNCTION then returns these numbers to the SUM FUNCTION. The SUM FUNCTION sums the numbers returned by the INDEX FUNCTION and the result is displayed in Cell D4.

 Some common errors that you should avoid when using this formula are leaving out the equal to sign and leaving spaces between characters.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.