SUM Index-Match: What is it, and How do I use it?Aug 01, 2022
When you find yourself handling large quantities of data within Excel, it is common to implement the INDEX and MATCH functions to search parameters under more than one criteria for sum.
This article will teach you how to incorporate SUM, SUMIF, and SUMPRODUCT alongside the INDEX-MATCH formula to sum under several criteria in Excel.
Introduction to the Functions: SUM, INDEX and MATCH with Examples
The objective of the SUM function is to sum all the numbers from a range of chosen cells. The formula syntax looks like this:
The objective of the INDEX function is to return a value of reference for the cell that intersects a chosen column and row in a selected range. Here is the formula syntax:
=INDEX(array, row_num, [column_num])
=INDEX(reference, row_num, [column_num], [area_num])
The objective of the MATCH function is to return the relative position of a chosen item in an array that mirrors a specific value in a specific order.
The formula syntax is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
Use of INDEX and MATCH Functions Together in Excel
Now you have an understanding of how to use INDEX and MATCH in excel; you can use the INDEX function and MATCH function together as a function. The INDEX and MATCH function is great for finding specific data from a big array. In this function, MATCH focuses on the row & column positions of the input values, whereas the INDEX function focuses on the output from the intersection of that column and row position.
Use of SUMIF with INDEX-MATCH Functions to Sum under Multiple Criteria
Before we move on to another type of combined formula, let's look at the SUMIF function.
The objective of this one of the sum functions is to add the cells specified by chosen criteria or conditions.
=SUMIF(range, criteria, [sum_range])
Use of SUMIFS with INDEX & MATCH Functions in Excel
SUMIFS is a sub-formula of the SUMIF formula. If you use the SUMIFS function with the INDEX and MATCH functions inside, you have the ability to add more than one criterion, which you can't do by just using the SUMIF function.
To do this, ensure you input your Sum Range, then Criteria Range, then Range Criteria.
Now you have an understanding of all of the methods we have discussed in this article; it's time to take this information and apply it to your next excel project. Keep an eye on the excel index function returns to make sure they match your expectations, and you'll quickly retrieve individual values faster and easier!
Frequently Asked Questions about Sum Index Match
How do I sum multiple cells with an INDEX match?
There are three easy ways to sum multiple cells with the INDEX match function:
SUM family function
How does a sum INDEX work?
The SUM function in Excel adds every number in a chosen range of cells and provides the sum of those values. The INDEX function offers the value at a chosen index in an array.
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.