How to Use SUMIF and SUMIFS with Wildcards to Total Selected Items in Excel
Question details
The user wants to calculate the total amount for specific items that meet a text-based condition using SUMIF or SUMIFS functions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summing values in a table where the item names match a specific text pattern, such as containing the letter 'e'.
- Observed behavior
- The user needs a formula to correctly apply wildcard criteria to sum the corresponding amounts for items like Bread, Donuts, Cookies, and Cakes.
Ensure your data is organized in a structured format, preferably as an Excel Table, with clear column headers for the items and the amounts you wish to sum.
Use SUMIFS with a Wildcard Criterion
Apply the SUMIFS function using asterisk wildcards to sum values based on partial text matches.
The SUMIFS function is highly versatile and allows you to sum values based on one or multiple criteria. By incorporating wildcards, you can easily filter and sum items that contain specific characters without needing exact matches.
Ensure your data is formatted as an Excel table (e.g., named Table1) with an [Items] column for text and an [Amount] column for numbers.
Click on the cell where you want the total to appear and type =SUMIFS( to begin the formula.
Add the sum range Table1[Amount] followed by a comma, then the criteria range Table1[Items] followed by another comma.
Type "*e*") to match any item containing the letter 'e'. The complete formula should look like =SUMIFS(Table1[Amount],Table1[Items],"*e*"). Press Enter to get the result.
Use SUMIF for a Single Text Condition
If you only have one condition to check, the simpler SUMIF function can also be used with wildcards.
Easily Calculate Conditional Sums in WPS Office
WPS Spreadsheet fully supports advanced functions like SUMIF and SUMIFS, including wildcard criteria. You can seamlessly manage, analyze, and calculate your data with high efficiency and a familiar interface.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook containing the data table.
- 2. Use the Function Wizard: Click the 'Formulas' tab, select 'Insert Function', and search for SUMIFS.
- 3. Input your parameters: Fill in the Sum_range, Criteria_range1, and Criteria1 (e.g., "*e*") in the dialog box, then click OK.
Frequently Asked Questions
What is the difference between SUMIF and SUMIFS?
SUMIF is designed to sum values based on a single condition, whereas SUMIFS can evaluate multiple conditions across different ranges before summing the values. SUMIFS is generally recommended as it is more flexible, even for single conditions.
Can I use other wildcards besides the asterisk (*)?
Yes, you can use a question mark (?) to represent a single character. For example, the criterion "C?ke" would match "Cake" or "Coke", but it would not match "Cookie".
Why is my SUMIFS formula returning 0?
This usually happens if the criteria text doesn't match any cells, if there are hidden trailing spaces in your data, or if the sum range and criteria range are not the exact same size.
How do I sum items that exactly match a specific word?
To sum items that exactly match a word, simply remove the wildcards from your criteria string. For example, use =SUMIFS(Table1[Amount], Table1[Items], "Bread").
