< Go Back

Random sort formula

HOW TO SUM/ RETURN AN ARRAY WITH THE INDEX FUNCTION

RETURNING AN ARRAY BY 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:

=SUM(INDEX(B6:B10,N(IF(1,{1,4,5}))))

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

Example

STEP 1

  • Type the data into the spreadsheet as shown below

 

STEP 2

  • Highlight Cell E6 and type in the string below

=SUM(INDEX(B6:B10,N(IF(1,{1,4,5}))))

STEP 3

  • 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!

Notes

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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar