Buy Now
SUM-index-match

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

SUM

The objective of the SUM function is to sum all the numbers from a range of chosen cells. The formula syntax looks like this:

=SUM(number1, [number2],…)

INDEX

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])

or, 

=INDEX(reference, row_num, [column_num], [area_num])

MATCH

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.

Concluding Words

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:

  1. SUM family function

  2. AGGREGATE function

  3. SUBTOTAL 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.

Related Articles

How to Delete Sheets in Excel: Deleting Multiple Sheets at Once

Microsoft Excel Certification: How to Become a Professional

Free Microsoft Excel Tutorial For Beginners

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.