VLOOKUP vs SUMIF: Which to Use for Totals

The short answer to VLOOKUP vs SUMIF: VLOOKUP finds one value from the first matching row, and SUMIF adds the numbers from every matching row. So for a total per customer, product or region, SUMIF is the right tool, and VLOOKUP gives a wrong answer whenever there's more than one match. This guide shows both on the same order list, including the cases where SUMIF can replace VLOOKUP and the traps when it does.

VLOOKUP vs SUMIF at a glance#

The two functions both search a column for a value, but they do different jobs with what they find.

How the two functions work#

VLOOKUP looks down the first column of a table for a value and returns the cell a set number of columns to the right:

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

FALSE means exact match. Without it, VLOOKUP uses approximate match, which needs sorted data. The VLOOKUP function guide explains each argument.

SUMIF checks one column against a condition and adds the matching numbers from another column:

=SUMIF(range, criteria, [sum_range])

The important difference: VLOOKUP stops at the first match. SUMIF keeps going to the end of the range and adds every match. The SUMIF function guide covers the criteria you can use.

The same data, solved both ways#

Here is an order list. Some customers have several orders:

Task 1: total spent by Acme#

=VLOOKUP("Acme",B2:D8,3,FALSE)
=SUMIF(B2:B8,"Acme",D2:D8)

VLOOKUP returns 120: the amount from row 2, Acme's first order. It never sees rows 4 and 7. SUMIF returns 1,020 (120 + 290 + 610), the correct total. No error warns you that VLOOKUP's answer is incomplete, which is what makes this mistake dangerous.

Task 2: the amount of order 1005#

Order numbers are unique, so there's only one match:

=VLOOKUP(1005,A2:D8,4,FALSE)
=SUMIF(A2:A8,1005,D2:D8)

Both return 950. With a unique key and a number to return, SUMIF works as a lookup.

Task 3: the product on order 1005#

=VLOOKUP(1005,A2:D8,3,FALSE)
=SUMIF(A2:A8,1005,C2:C8)

VLOOKUP returns Chairs. SUMIF returns 0, because it can only add numbers and the Product column holds text. For text, you need a lookup function.

Task 4: a customer who isn't in the list#

=VLOOKUP("Elm",B2:D8,3,FALSE)
=SUMIF(B2:B8,"Elm",D2:D8)

VLOOKUP shows #N/A: not found. SUMIF shows 0. In a sales summary, 0 is the right answer for a customer with no orders. In a lookup, a 0 can hide a typo such as "Acme " with a space.

Task 5: looking to the left#

To find which order belongs to a known amount, say 610, VLOOKUP can't help: the Order column is to the left of Amount. SUMIF doesn't care about column order:

=SUMIF(D2:D8,610,A2:A8)

This returns 1006. It works only because 610 appears once. For left lookups in general, INDEX and MATCH or XLOOKUP are safer; see VLOOKUP vs INDEX MATCH.

When to use VLOOKUP#

Use VLOOKUP (or its modern replacement, XLOOKUP) when you need one value about one item:

• Returning text, such as a customer name, product description or status.
• Pulling a price or rate from a list where each item appears once.
• Band or tier lookups, such as tax bands or commission rates, using TRUE for approximate match. SUMIF can't do this.
• When a missing value should shout. #N/A tells you the item isn't there, which is often what you want in a lookup.

If you have Excel 2021 or Microsoft 365, XLOOKUP is easier still: it looks left, defaults to exact match and doesn't use column numbers. The comparison of VLOOKUP vs XLOOKUP explains the switch. Practise VLOOKUP exact match to master the classic version first.

When to use SUMIF#

Use SUMIF when you need a total, or when 0 is a sensible answer for "nothing found":

• Totals per group: per customer, product, region, month or category.
• Summary tables where some groups may have no rows yet. 0 looks cleaner than #N/A.
• Numeric lookups with unique keys in a sheet that already uses SUMIF, especially when the value you want is to the left of the key.
• Conditions beyond equal to, like ">300" or "Ac*", which VLOOKUP can't use.

A typical summary table lists each customer once and fills one SUMIF down. With the customer names in F2:F5, type this in G2 and fill down:

=SUMIF($B$2:$B$8,F2,$D$2:$D$8)

Acme shows 1,020, Birch 1,290, Cobalt 80 and Delta 150. A quick check is that these add up to =SUM(D2:D8), which is 2,540. If they don't, a name in the list is spelled differently from the data. The $ signs keep the ranges fixed while F2 moves down to F3, F4 and F5.

For two or more conditions, such as Acme and Toner, switch to SUMIFS. The comparison of SUMIF vs SUMIFS shows how. Practise SUMIF to build totals like these in a live sheet.

Common mistakes with VLOOKUP and SUMIF#

These are the slips that most often give wrong results:

1. Using VLOOKUP for a total. It returns the first match only. If a value can appear more than once, use SUMIF.
2. Using SUMIF as a lookup on duplicate keys. If order 1004 is accidentally entered twice, SUMIF adds both rows and returns double the amount, with no warning. Check uniqueness with =COUNTIF(A2:A8,1004).
3. Expecting SUMIF to return text. It returns 0. Use VLOOKUP or XLOOKUP.
4. Forgetting FALSE in VLOOKUP. Without it, VLOOKUP uses approximate match and can return a value from the wrong row in unsorted data.
5. Trusting a 0 from SUMIF. A 0 may mean "no orders" or "the name is misspelled". Spot-check with COUNTIF.
6. Hard-coded column numbers. Inserting a column inside a VLOOKUP table shifts the column you meant. SUMIF points at the column directly, so it's not affected. The comparison of VLOOKUP vs HLOOKUP shows the same column-number issue in the horizontal version.

This exact choice comes up in Excel tests: a question asks for a customer's total, and VLOOKUP is the tempting wrong answer. The article on Excel test questions and answers has more examples, and the intermediate Excel test lets you practise under time pressure.

Our recommendation#

Match the function to the question. If the answer is a total that could come from several rows, use SUMIF. If the answer is one value about one item, especially text, use VLOOKUP, or XLOOKUP if everyone has Excel 2021 or Microsoft 365. Use SUMIF as a lookup only for numbers with keys you know are unique.

In short: SUMIF for totals, VLOOKUP for single values.

Practise it#

Try the exercise on this page: VLOOKUP would return only Acme's first order, so SUMIF is the right choice. Then practise SUMIF in a live spreadsheet and practise VLOOKUP exact match to see the difference on real data.