Excel SUMIF with VLOOKUP for Specific Text

Formulas & functions · Updated November 22, 2026

To sum values based on a specific text that is not in the same column as the values, combine SUMIF with VLOOKUP using an array formula: =SUM(SUMIF(VLOOKUP(lookup_range, table_array, col_index, FALSE), criteria, sum_range)) — but the simpler, more reliable approach is to use SUMPRODUCT or SUMIFS with an INDEX/MATCH helper column. This article explains the exact formulas, when each works, and common pitfalls.

Why Combine SUMIF and VLOOKUP?

Excel’s SUMIF sums cells that meet a condition, but it only works when the criteria range and the sum range are in the same worksheet and the criteria is a direct cell value. VLOOKUP finds a value in a table and returns a corresponding value from another column. When you need to sum amounts based on a text that appears in a lookup table (e.g., sum sales for a product category that is stored in a separate reference table), you might be tempted to use VLOOKUP inside SUMIF. However, SUMIF does not natively accept an array of lookup results as its criteria range in older Excel versions, which is why a direct combination often returns #VALUE! or incorrect results.

The Standard SUMIF Formula

First, recall the basic SUMIF syntax: =SUMIF(range, criteria, [sum_range]). For example, with product names in A2:A20 and sales in B2:B20, to sum sales for “Widget” you write =SUMIF(A2:A20, "Widget", B2:B20). This works fine when the criteria text is in the same column as the range you check.

Using VLOOKUP Inside SUMIF (and Why It Fails)

A common attempt is =SUMIF(VLOOKUP(A2:A20, lookup_table, 2, FALSE), "Widget", B2:B20). This fails because VLOOKUP is not designed to return an array of multiple values when given a range as its lookup_value — it returns a single value (the first match) or a #N/A error. Even if you press Ctrl+Shift+Enter to force an array, SUMIF does not evaluate the array as a range; it treats it as a single value. So the formula returns 0 or an error.

The Reliable Workaround: SUMPRODUCT with VLOOKUP

Instead, use SUMPRODUCT to force array evaluation. Suppose you have a sales table: column A has product IDs, column B has sales amounts (B2:B20). A separate lookup table has product IDs in D2:D10 and product names in E2:E10. To sum sales for the product named “Widget”, use:

=SUMPRODUCT((VLOOKUP(A2:A20, D2:E10, 2, FALSE)="Widget") * B2:B20)

This works because SUMPRODUCT treats VLOOKUP(A2:A20, ...) as an array formula, returning an array of product names for each ID in A2:A20, then compares each to “Widget”, and multiplies the resulting TRUE/FALSE array by the sales amounts. The arithmetic operation coerces TRUE/FALSE to 1/0, and SUMPRODUCT sums the products. In this example, if A2:A20 has IDs 101–119, and D2:E10 maps 101 to “Widget”, 102 to “Gadget”, etc., the formula returns the total sales for all rows where the ID maps to “Widget”.

Better Alternative: SUMIFS with a Helper Column

For clarity and performance, add a helper column that uses VLOOKUP to fetch the text for each row. For instance, in column C (C2:C20), enter =VLOOKUP(A2, D$2:E$10, 2, FALSE) and copy down. Then use a simple SUMIF on the helper column: =SUMIF(C2:C20, "Widget", B2:B20). This is easier to audit and works in all Excel versions. If you prefer a single formula without a helper, use SUMPRODUCT as shown above.

Using SUMIFS with INDEX/MATCH (No Helper)

If you have Excel 2021 or Microsoft 365, you can use SUMIFS with an array of lookup results by entering it as a dynamic array formula. For example, with the same layout, in a single cell: =SUMIFS(B2:B20, INDEX(D2:E10, MATCH(A2:A20, D2:D10, 0), 2), "Widget"). This uses INDEX and MATCH to return an array of names, and because SUMIFS in dynamic array Excel can handle array criteria, it works without Ctrl+Shift+Enter. However, this formula is more complex and may be less readable than the helper column approach.

Common Problems and Solutions

  • #N/A errors: If a product ID in A2:A20 is not found in the lookup table, VLOOKUP returns #N/A. Use IFERROR in the helper column: =IFERROR(VLOOKUP(...), ""), or wrap the whole SUMPRODUCT with IFERROR.
  • Case sensitivity: SUMIF and VLOOKUP are case-insensitive by default. If you need exact case matching, use EXACT in an array formula or use SUMPRODUCT with EXACT.
  • Wildcards: SUMIF supports wildcards like * and ?. If your criteria text contains an asterisk, escape it with a tilde (~).
  • Multiple criteria: If you need to sum based on two or more conditions (e.g., product name and region), use SUMIFS instead of SUMIF.

Performance and File Size

Array formulas like SUMPRODUCT can slow down large worksheets because they calculate multiple times. For datasets with thousands of rows, a helper column with VLOOKUP is faster because it calculates once per row. Also, avoid using full-column references (e.g., A:A) in array formulas; limit ranges to the actual data area to reduce calculation time.

Frequently Asked Questions

Can I use VLOOKUP directly inside SUMIF in Excel 365?

In Excel 365, SUMIF still does not accept an array from VLOOKUP as the range argument. You must use SUMPRODUCT or a helper column. However, you can use SUMIFS with INDEX/MATCH as shown above, which works in dynamic array Excel.

What if my lookup table has duplicate text values?

VLOOKUP returns the first match only. If you need to sum across multiple matches, consider using SUMIFS directly on the lookup table if the text is in the same table, or restructure your data.

How do I sum based on partial text match?

Use wildcards in the criteria: =SUMIF(A2:A20, "*Widget*", B2:B20). If the text is in a lookup table, first create a helper column with VLOOKUP, then use wildcards on that helper column.

Why does my formula return 0 instead of a sum?

Check that the criteria text matches exactly (no extra spaces), the ranges are the same size, and the lookup table is sorted correctly if using approximate match. Also ensure you are not using VLOOKUP incorrectly with a range as the lookup value.

Is there a way to avoid helper columns entirely?

Yes, use SUMPRODUCT as described. It is a single formula but may be harder to read. For complex conditions, consider using LET to define the lookup array once: =LET(names, VLOOKUP(A2:A20, D2:E10, 2, FALSE), SUMPRODUCT((names="Widget")*B2:B20)) — available in Excel 365.

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.