HOW TO SUM/ RETURN AN ARRAY WITH THE INDEX FUNCTION
The INDEX function can help you return an ARRAY of values to another function like the SUM function by integrating INDEX with the IF and N functions. This combination of functions can enable you to sum up several items in a list when you want only the values of specific cells to be added. In the example below, the formula in Cell E6 is given as:
Some common errors that you should avoid when using this formula are leaving out the equal to sign and leaving spaces between characters.
RETURNING AN ARRAY BY THE INDEX FUNCTION
- Type the data into the spreadsheet as shown below
- Highlight Cell E6 and type in the string below
- Click the ENTER button. When you do the same process for Cell E7, you will have the result below
This formula has enabled us to effectively SUM the values that are present in Cells B6, B9, and B10. Can you try that of Cell E7? I am positive you can!
The formula is believed to function in this way. The IF function returns as TRUE any number that is contained in the parenthesis because the only argument 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 VALUES (B6:B10) for their positions within the VALUE COLUMN. The INDEX FUNCTION then reads the digits in the format below:
- 1 is returned or read as 49
- 4 is returned or read as 80
- 5 is returned or read as 99
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 E6.