Excel SUM with INDEX MATCH for Multiple Criteria

Formulas & functions · Updated November 22, 2026

The direct answer: use SUM(INDEX(return_range, MATCH(1, (criteria1_range=criteria1) * (criteria2_range=criteria2), 0))) as an array formula (or with SUMPRODUCT for a non-array alternative). This sums values in a column where two or more conditions are met, combining the lookup power of INDEX/MATCH with the aggregation of SUM.

Why Not Just SUMIFS?

Excel’s SUMIFS function is the go-to for summing with multiple criteria, and for most cases it’s simpler and faster. However, SUMIFS requires that the sum range and criteria ranges are separate columns. When your data is structured horizontally (e.g., criteria in rows, values in a matrix) or when you need to perform a lookup to find the correct column to sum, SUMIFS falls short. INDEX/MATCH combined with SUM gives you the flexibility to first locate the exact row and column intersection, then sum across that range. This is especially useful when your criteria are in a lookup table and the values to sum are in a different sheet or a non-contiguous layout.

How the Formula Works

Assume you have a sales table: Product in A2:A20, Region in B2:B20, and Sales in C2:C20. You want the total sales for “Widget” in “East”. The formula in cell E2 would be:

=SUM(INDEX(C2:C20, MATCH(1, (A2:A20="Widget") * (B2:B20="East"), 0)))

But this only returns the first match. To sum all matches, you need to use SUMPRODUCT or enter it as an array formula (Ctrl+Shift+Enter in older Excel). The correct array version is:

=SUM(IF((A2:A20="Widget") * (B2:B20="East"), C2:C20))

Entered with Ctrl+Shift+Enter in Excel 2019 and earlier. In Microsoft 365, it works as a dynamic array without CSE. The IF checks each row, returns the sales value if both conditions are true, otherwise FALSE, and SUM adds them.

If you specifically want to use INDEX/MATCH to find a row and then sum a range from that row (e.g., sum all monthly figures for a specific product), you can do:

=SUM(INDEX(B2:M20, MATCH("Widget", A2:A20, 0), 0))

Here, MATCH finds the row number for “Widget” in column A, and INDEX returns the entire row (columns B to M) as a reference, which SUM adds. This works because INDEX with row number 0 returns the whole row.

Using SUMPRODUCT for Multiple Criteria Sum

The most robust and commonly used method for summing with multiple criteria without array entry is SUMPRODUCT. It handles arrays natively. Using the same layout:

=SUMPRODUCT((A2:A20="Widget") * (B2:B20="East") * C2:C20)

This multiplies the boolean arrays (TRUE/FALSE) which become 1/0, and sums the products. It’s not strictly INDEX/MATCH, but it’s the standard alternative. If you must use INDEX/MATCH within SUMPRODUCT, you can do:

=SUMPRODUCT((A2:A20="Widget") * (B2:B20="East") * (C2:C20))

That’s the same as above. To combine INDEX/MATCH with SUMPRODUCT for a lookup-then-sum scenario, you might use:

=SUMPRODUCT((A2:A20="Widget") * (B2:B20="East") * INDEX(C2:E20, 0, MATCH("Q1", C1:E1, 0)))

This finds the column for “Q1” in headers C1:E1, then sums the values in that column for rows where product is “Widget” and region is “East”.

Step-by-Step: Building the Formula

  1. Identify your criteria ranges and the sum range. For example, criteria1 in A2:A20, criteria2 in B2:B20, sum in C2:C20.
  2. Write the condition pairs: (A2:A20="Widget") and (B2:B20="East").
  3. Multiply them with the sum range: (A2:A20="Widget") * (B2:B20="East") * C2:C20.
  4. Wrap in SUMPRODUCT to avoid array entry, or use SUM(IF(...)) with Ctrl+Shift+Enter in older Excel.
  5. If you need to dynamically pick the sum column, use INDEX to return that column: INDEX(C2:E20, 0, MATCH("Q1", C1:E1, 0)).

For Microsoft 365, you can also use the FILTER function: =SUM(FILTER(C2:C20, (A2:A20="Widget") * (B2:B20="East"))). This is cleaner and doesn’t require array entry.

Common Pitfalls and Errors

  • Wrong data types: If criteria are numbers stored as text, the match fails. Use VALUE or ensure consistent formatting.
  • Array formula not entered correctly: In Excel 2019 and earlier, forgetting Ctrl+Shift+Enter gives a #VALUE! error. In Microsoft 365, it’s automatic.
  • Using entire columns: =SUMPRODUCT((A:A="Widget")*...) works but can slow down. Limit to actual data range.
  • Blank cells in criteria: If a criteria cell is blank, it matches empty strings, not blanks. Use "" or ISBLANK.
  • Case sensitivity: Both SUMPRODUCT and SUMIFS are case-insensitive. For case-sensitive, use EXACT inside SUMPRODUCT.

INDEX/MATCH vs. SUMIFS vs. SUMPRODUCT

Here’s a quick comparison to help you choose:

  • SUMIFS: Fastest, easiest to read, but requires sum range as a separate column. Use when your data is a simple table.
  • SUMPRODUCT: Flexible, handles arrays without CSE, works with any range shape. Use when you need to multiply conditions or when sum range is derived from a lookup.
  • INDEX/MATCH with SUM: Best when you need to locate a specific row or column first, then sum that entire row/column. Also useful for dynamic column selection.

For example, if you have a matrix of products (rows) and months (columns), and you want to sum all months for a specific product, SUM(INDEX(matrix, MATCH(product, product_list, 0), 0)) is elegant. SUMIFS cannot do that directly because it can’t sum a row.

Frequently Asked Questions

Can I use INDEX/MATCH with multiple criteria in a SUMIFS-like way?

Yes, but it’s not the intended use. You can use SUM(INDEX(return_range, MATCH(1, (crit1=val1)*(crit2=val2), 0))) but it returns only the first match. To sum all matches, you need SUMPRODUCT or an array formula with IF. For most multi-criteria sums, SUMIFS or SUMPRODUCT are better.

Why does my formula return #VALUE! or #N/A?

#VALUE! often means you didn’t enter an array formula correctly in older Excel (need Ctrl+Shift+Enter). #N/A means the MATCH didn’t find the criteria. Check for typos, extra spaces, or number/text mismatches. Also ensure the ranges are the same size.

Is there a way to sum with multiple criteria without using array formulas?

Yes, use SUMPRODUCT. It handles arrays natively and doesn’t require Ctrl+Shift+Enter. For example, =SUMPRODUCT((A2:A20="Widget")*(B2:B20="East")*C2:C20) works in all Excel versions.

Does this work in Google Sheets?

Yes, both SUMPRODUCT and SUMIFS work in Google Sheets with the same syntax. Google Sheets also supports FILTER and SUM with array formulas, but you don’t need Ctrl+Shift+Enter; it’s automatic. The main difference is that Google Sheets uses ArrayFormula wrapper for some functions, but SUMPRODUCT works as-is.

Can I use wildcards with INDEX/MATCH or SUMPRODUCT?

Wildcards (*, ?) work in SUMIFS but not directly in SUMPRODUCT or MATCH with equality. For partial matches in SUMPRODUCT, use ISNUMBER(SEARCH("Widget", A2:A20)) which is case-insensitive. For case-sensitive, use FIND.

How do I sum across multiple sheets with multiple criteria?

You can use SUMPRODUCT with 3D references, but it’s tricky. A simpler way is to use SUMIFS on each sheet and add them, or consolidate data into one sheet. For example: =SUMIFS(Sheet1!C:C, Sheet1!A:A, "Widget", Sheet1!B:B, "East") + SUMIFS(Sheet2!C:C, Sheet2!A:A, "Widget", Sheet2!B:B, "East"). For dynamic sheet lists, you’d need VBA or INDIRECT.

Real-World Example: Sales Dashboard

Suppose you have a sales table in A1:D100 with columns: Date, Product, Region, Sales. You want to sum sales for “Widget” in “East” for the month of January. You could use:

=SUMPRODUCT((B2:B100="Widget") * (C2:C100="East") * (TEXT(A2:A100,"mmm")="Jan") * D2:D100)

This uses TEXT to extract the month. Alternatively, use SUMIFS with date criteria: =SUMIFS(D2:D100, B2:B100, "Widget", C2:C100, "East", A2:A100, ">="&DATE(2024,1,1), A2:A100, "<="&DATE(2024,1,31)). The SUMPRODUCT version is more flexible if you have multiple conditions that aren't simple equality.

For a dynamic column selection, imagine you have a matrix where rows are products and columns are months (Jan-Dec). To sum all months for "Widget", use:

=SUM(INDEX(B2:M20, MATCH("Widget", A2:A20, 0), 0))

This returns the sum of the entire row for Widget. If you only want Q1, change the column range: =SUM(INDEX(B2:D20, MATCH("Widget", A2:A20, 0), 0)).

Performance and Best Practices

  • Avoid full-column references in SUMPRODUCT (e.g., A:A) as it calculates over 1 million rows. Use specific ranges like A2:A1000.
  • In Microsoft 365, prefer FILTER and SUM for readability and speed.
  • For large datasets, SUMIFS is usually faster than SUMPRODUCT because it's optimized.
  • Use named ranges to make formulas easier to read and maintain.
  • If you need to sum with many criteria (more than 5), consider using a helper column that concatenates criteria, then use SUMIF on that helper.

Remember that SUMPRODUCT treats boolean TRUE as 1 and FALSE as 0 when multiplied. Always ensure your criteria ranges are the same size to avoid #VALUE! errors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.