Solved: How do I return a sum if a word in a column matches from a list?

📌 The Problem Explained

Imagine you are managing a small grocery business. You have a daily sales log in Column A (Item Name) and Column B (Sale Amount). You want to calculate the total revenue, but only for a specific subset of items (e.g., “Apples,” “Oranges,” and “Bananas”).

Instead of writing a long formula like SUMIF(item="Apples") + SUMIF(item="Oranges")..., you want the formula to look at a separate “Target List” elsewhere in your sheet and sum the totals automatically if any word in your sales log matches an entry in that list.

💡 The Quick Solution

The most efficient way to handle this in Google Sheets is to wrap a SUMIF function inside a SUMPRODUCT function.

=SUMPRODUCT(SUMIF(A2:A100, D2:D5, B2:B100))

• A2:A100: The column containing the words you want to check.
• D2:D5: The list of words you are looking for.
• B2:B100: The column containing the numbers you want to sum.

🛠️ Step-by-Step Breakdown

Follow these steps to implement the formula in your spreadsheet:

Step	Action	Description
1	Prepare your Data	Ensure your main data is in two columns (e.g., Categories in A and Values in B).
2	Create the List	Write the words you want to match in a separate range (e.g., D2:D5).
3	Enter Formula	Click the cell where you want the result and type the SUMPRODUCT formula.
4	Verify Ranges	Ensure the “Sum Range” and “Criteria Range” are the same height (e.g., 2 to 100).

Why this works:

1. The SUMIF function usually looks for one thing. By giving it a range (D2:D5), it creates an internal list of sums for every item in your list.
2. The SUMPRODUCT function then adds those individual sums together to give you one final grand total.

⚠️ Common Mistakes to Avoid

• Mismatched Range Sizes: If your criteria column is A2:A100, your sum column must also be B2:B100. If one is shorter than the other, you will receive an #N/A or #VALUE! error.
• Hidden Spaces: If the word in your list is “Apple” but your data column contains “Apple “ (with a trailing space), the formula will not find a match. You can fix this by using Data > Data cleanup > Trim whitespace on your columns.
• Using SUM instead of SUMPRODUCT: If you use =SUM(SUMIF(...)), it may only return the sum for the first item in your list unless you press Ctrl + Shift + Enter to turn it into an Array Formula. SUMPRODUCT is preferred because it handles arrays automatically.