Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to look up a value and sum the matches in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=SUMIF(A2:A6,D2,B2:B6)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Put your lookup key in a cell (e.g. D2).
- 2Enter =SUMIF(A2:A6,D2,B2:B6) in the result cell.
- 3The first argument (A2:A6) is the range to search; D2 is the criterion; B2:B6 is the range to sum.
- 4Press Enter. The formula returns the total of all B values where the A value matches D2.
Tips
- Use SUMIFS when you need more than one condition: =SUMIFS(sum_range, range1, criteria1, range2, criteria2).
- Wildcards work in SUMIF criteria: =SUMIF(A2:A6,"North*",B2:B6) sums all rows where A starts with 'North'.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What is the difference between SUMIF and VLOOKUP for summing?
VLOOKUP returns the value from the first matching row. SUMIF adds up all matching rows, making it the right choice when there are multiple matches.
Can I sum across multiple sheets?
SUMIF does not natively sum across multiple sheets. Use a 3D reference with SUM or combine SUMIF with INDIRECT for a workaround.
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.
How-toHow to look up a value across multiple columns
Use MATCH with a combined range or INDEX to search for a value across several columns and return the column it was found in or its adjacent value.