How to Sum Excel Values When Another Column Contains Specific Text
Question details
Calculate the total of numerical values in one column based on matching specific text criteria located in another column.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating conditional totals when handling data with categories, names, or repeated text values across multiple columns.
- Observed behavior
- Needs to calculate the sum of numbers in a specific range that correspond to matching text items in an adjacent or specified range.
Ensure your text criteria and numerical data ranges are of equal length, and check for any accidental leading or trailing spaces in your text cells, as they can cause formula matching errors.
Use the SUMIF Function for a Single Text Criterion
The SUMIF function is the most direct way to sum numbers based on a single specific text match in another column.
The SUMIF syntax requires three arguments: the range containing the text to evaluate, the specific text criterion you are looking for, and the range containing the values to sum.
Click on the cell where you want the calculated total to appear.
Type =SUMIF(A2:A33, D2, B2:B33). In this example, A2:A33 is the column with the text, D2 contains the specific text you want to match, and B2:B33 is the column containing the numbers to sum.
Press Enter to calculate the result. If you have a list of different text criteria, click the bottom-right corner of the cell and drag the fill handle down to copy the formula for each item.
Use the SUMIFS Function for Multiple Criteria
When you need to sum values based on more than one text condition across multiple columns, SUMIFS is the appropriate function.
Create a PivotTable to Summarize by Text Categories
If you have many repeated text values and want a summary of all categories at once without writing formulas, a PivotTable is highly efficient.
Easily Sum Data Based on Text with WPS Office
WPS Spreadsheet fully supports SUMIF, SUMIFS, and PivotTables, allowing you to seamlessly calculate conditional totals exactly as you would in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Start the formula: Click on the cell where you want the sum and type =SUMIF( to trigger the formula prompt.
- 3. Select ranges: Follow the on-screen formula hint to highlight your text criteria range, specify your criterion, and select your sum range.
- 4. Get results: Press Enter to get your conditional sum instantly.
Frequently Asked Questions
Can I use partial text matches with the SUMIF function?
Yes, you can use wildcard characters in your criteria. An asterisk (*) represents any sequence of characters, and a question mark (?) represents a single character. For example, using "*apple*" as your criterion will sum all values where the text contains the word "apple" anywhere in the cell.
Why is my SUMIF formula returning zero even though the text matches?
This usually happens due to hidden leading or trailing spaces in either your criteria cell or the text column itself. You can use the TRIM function on your source data to remove extra spaces, or verify that the text matches exactly.
Is the SUMIF function case-sensitive?
No, the SUMIF and SUMIFS functions are not case-sensitive. Searching for "Apple", "APPLE", or "apple" as your text criterion will yield the exact same sum result.
How do I sum values if the text is NOT equal to a specific word?
You can use the "not equal to" operator (<>) within your criterion. For example, to sum all values where the text in column A is not "Apples", you would use the formula: =SUMIF(A2:A33, "<>Apples", B2:B33).
