SUMIF with INDEX and MATCH Functions in Excel

In Microsoft Excel, the SUMIF with INDEX-MATCH functions is widely used to extract the sum based on multiple criteria from different columns & rows. In this article, you’ll get to know in detail how we can use this SUMIF along with INDEX-MATCH functions effectively to pull out data under multiple criteria.

The above screenshot is an overview of the article which represents the dataset & an example of the function to extract data by summation based on criteria. You’ll get to learn more about the dataset along with all suitable functions in the following methods in this article.

Introduction to SUMIF, INDEX & MATCH Functions in Excel

Before getting down to the uses of this combined function, let’s get introduced to the inner & basic functions at first.

1. SUMIF Function

• Activity:

Add the cells specified by the given conditions or criteria.

• Formula Syntax:

=SUMIF(range, criteria, [sum_range])

• Arguments:

range- Range of cells where the criteria lies.

criteria- Selected criteria for the range.

sum_range- Range of cells that are considered for summing up.

• Example:

In the picture below, a dataset is present. 10 computer brands are in Column A, device categories in Column B and total sales of product for each brand in 6 months are lying in next 6 columns in the table.

With the SUMIF function, we’ll find the total sales in the month of May for desktops only of all brands. So, our formula in Cell F18 will be:

After pressing Enter, you’ll get the total sales price as $ 71,810.

2. INDEX Function

• Activity:

Returns a value of reference of the cell at the intersection of the particular row & column in a given range.

• Formula Syntax:

=INDEX(array, row_num, [column_num])

Or, 

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

• Arguments:

array- Range of cells, columns or rows considered for the values to lookup.

row_num- Row position in the array.

column_position- Column position in the array.

reference- Range of arrays.

area_num- Serial number of array in the reference, if you don’t mention it’ll consider as 1.

• Example:

Assuming that we want to know the value at the intersection of the 3rd row & 4th column from the array of sales prices from the table. So, in Cell F18, we have to type:

Now Press Enter & you’ll get the result.

Since the 4th column in the selected array represents the selling prices of all devices for April & the 3rd row represents the Lenovo Desktop category, so at their intersection in the array, we’ll find the selling price of Lenovo Desktop in April.

3. MATCH Function

• Activity:

Returns the relative position of an item in an array that matches a specified value in a specified order.

• Formula Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

• Arguments:

lookup_value- Cell value that is to be looked for in the range of cells.

lookup_array- Range of cells where lookup value has to be searched for.

match_type- It’s optional. It’ll determine if you want partial or exact match from the array for your lookup value.

• Example:

At first, we’re going to know the position of the month June from the month headers. In Cell F17, our formula will be:

Press Enter & you’ll find that the column position of the month June is 6 in the month headers.

Change the name of the month in Cell F16 & you’ll see the related column position of another month selected.

And if we want to know the row position of the brand Dell from the names of the brands in Column B, then the formula in Cell F20 will be:

Here, B5:B14 is the range of cells where the name of the brand will be looked for. If you change the brand name in Cell F19, you’ll get the related row position of that brand from the selected range of cells.

Combining INDEX & MATCH Functions in Excel

Now we’ll know how to use INDEX & MATCH functions together with examples as a function and what exactly this combined function returns as output. This combined INDEX-MATCH function is effective to find specific data from a large array. The MATCH function here looks for the row & column positions of the input values & the INDEX function will simply return the output from the intersection of that row & column positions.

Now, based on our dataset, we want to know the total selling price of the Lenovo brand in June. So, in Cell F18, type:

Press Enter & you’ll find the result instantly.

If you change the month & device name in F16 & F17 respectively, you’ll get the related result in F18 at once.

Use of SUMIF with INDEX & MATCH Functions in Excel

Now let’s come to the main talking point of the article. We’ll use SUMIF with INDEX & MATCH functions here. For our calculation with multiple criteria, we’ve modified the dataset a bit. In Column A, 5 brands are now present with multiple appearances for their 2 types of devices. Sales prices in the rest of the columns are unchanged.

We’ll find out the total sales of Lenovo devices in the month of June.

📌 Steps:

➤ In the output Cell F18, the related formula will be:

➤ Press Enter & you’ll get the total sales price for Lenovo in June at once.

And if you want to switch to the device category, assuming you want to find the total sales price for the desktop then our Sum Range will be C5:C14 & Sum Criteria will be Desktop now. So, in that case the formula will be:

Use of SUMIFS with INDEX & MATCH Functions in Excel

SUMIFS is the sub-category of the SUMIF function. By using the SUMIFS function along with INDEX & MATCH functions inside, you can add more than 1 criterion which is not possible with SUMIF function. In SUMIFS function, you have to input the Sum Range first, then Criteria Range as well as Range Criteria will be placed. Now based on our dataset, we’ll find out the sales price of Acer desktop in the month of May. Along the rows, we’re adding two different criteria here from Columns B & C.

📌 Steps:

➤ The related formula in Cell F19 will be:

➤ Press Enter & the function will return as $ 9,000.00.

Download Practice Workbook

You can download our Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope, this article on the uses of SUMIF with INDEX & MATCH functions will now prompt you to apply in your Excel chores. If you got any questions or feedback, please let me know in the comment section. Or you can have a look at our other interesting articles related to Excel functions on this website.

<< Go Back to INDEX MATCH | Formula List | Learn Excel