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.

sumif index match overview in excel

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.

sumif introduction in excel

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.

sumif introduction in excel

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


=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.

index introduction in excel

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.

match introduction in excel

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.

match introduction in excel

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 index max in excel

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.

sumif index match in excel

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.

sumif index match in excel

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:


sumif with index match in excel

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.

sumifs index match in excel

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

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

  1. SUMIFS Columns Multiple with INDEX and MATCH Functions in Excel

  2. Thank you for this.

    In your first example in “Use of SUMIF with INDEX & MATCH Functions in Excel” section; how about if I want to find out the total sales for June YTD? So summing all of the months?

  3. Find Total number of Sheets for “.040 ALUM KYNAR”
    ” “.032 ALUM KYNAR” etc. Thank You

    A B
    0 0
    0 0
    0 0
    .040 ALUM KYNAR 9
    0 0
    0 0
    .040 ALUM KYNAR 14
    0 0
    .032 ALUM KYNAR 20
    0 0
    – –
    0 0
    0 0
    – –
    .032 ALUM KYNAR 6
    0 0
    0 0
    .040 ALUM KYNAR 5
    .050 ALUM MILL 3
    0 0
    .050 ALUM KYNAR 9

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 7, 2024 at 5:40 PM

      Hello GARY SHERMAN

      Thanks for reaching out and sharing your problem. You want to find the total number of sheets for each material type. To do so, you can use the SUMIF function to reach your goal.

      Follow these steps:

      1. Select an empty cell.

      2. Insert the following formula: =SUMIF($A$2:$A$22,$D5, $B$2:$B$22)

      3. Hit Enter.

      4. Use the Fill Handle icon to copy the formula down.

      As a result, you will see an output like the following image.

      Hopefully, the idea will help; Good luck.

      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF