How to Use SUMIF with Text Contained in a Cell
Question details
The user needs to sum values based on whether corresponding cells contain a specific text string using wildcard criteria.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Adding up numerical values in a specific range when the criteria range contains target words surrounded by other text, such as a collection name.
- Observed behavior
- The user requires the correct formula syntax to assign the sum range and criteria range properly, using asterisks to match partial text.
Ensure your dataset is organized into distinct columns, with one column containing the text descriptions and another containing the numerical values you wish to sum.
Use SUMIF with Asterisk Wildcards
Use asterisks (*) around your criteria text to match any characters before or after the target phrase within the cell.
The SUMIF function allows you to sum values based on partial text matches by incorporating wildcards. An asterisk (*) acts as a wildcard character that represents any sequence of characters.
Click on the empty cell where you want the total sum to be displayed.
Type the formula =SUMIF(E7:E120, "*Allied Health*", F7:F120), assuming column E contains your text descriptions and column F contains the numerical values to add.
Press Enter to apply the formula and calculate the sum. If your text and values are in opposite columns, reverse the ranges in the formula: =SUMIF(F7:F120, "*Allied Health*", E7:E120).
Master Formulas and Functions with WPS Spreadsheet
WPS Spreadsheet provides full support for advanced functions like SUMIF, including wildcard criteria, making your data analysis quick and accurate.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your dataset.
- 2. Select a cell: Click on the specific cell where you want the sum result to appear.
- 3. Insert the SUMIF function: Go to the 'Formulas' tab and click 'Insert Function' to search for SUMIF, or directly type =SUMIF( into the cell.
- 4. Apply wildcard criteria: Select your criteria range, type your wildcard text (e.g., "*text*"), select the sum range, and press Enter to calculate.
Frequently Asked Questions
Can I reference a cell instead of typing the text directly in the SUMIF formula?
Yes, you can concatenate wildcards with a cell reference using an ampersand (&). For example, use the formula =SUMIF(A1:A10, "*"&C1&"*", B1:B10) where cell C1 contains your target text.
Does the SUMIF wildcard formula differentiate between uppercase and lowercase letters?
No, the SUMIF function is not case-sensitive. It will successfully match your target text regardless of capitalization.
What if I only want to match text that ends with a specific word?
Place the asterisk only at the beginning of your text criteria. For example, using "*Health" will sum only the cells where the text string ends exactly with the word 'Health'.
