How to Use SUMIFS with Included and Excluded Criteria in Spreadsheets
Question details
The user needs to calculate conditional sums using the SUMIFS function, applying multiple criteria where some conditions must be met and others must be explicitly excluded.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating totals based on specific text symbols in one column while including or excluding specific text records (like 'Discontinued') in another column.
- Observed behavior
- The user wants to correctly formulate the SUMIFS formula to apply both equality and inequality logical operators across multiple criteria ranges.
Ensure your data ranges are consistent in size; the sum range and all criteria ranges in a SUMIFS formula must contain the exact same number of rows and columns to prevent calculation errors.
Using SUMIFS with Exact Match and Exclusion Logic
Apply the SUMIFS function using standard text matching for inclusion and the not-equal-to operator (<>) for exclusion.
The SUMIFS function allows you to sum cells that meet multiple conditions. To exclude specific text or numbers, you must combine the logical operator '<>' (does not equal) with your target criteria inside double quotation marks.
Click on the blank cell where you want the conditional sum result to be displayed.
To sum values where a specific symbol (e.g., '÷') appears and the status is exactly 'Discontinued', type `=SUMIFS(C4:C7, D4:D7, "÷", E4:E7, "Discontinued")`. Replace the cell ranges (C4:C7, D4:D7, etc.) with your actual data ranges.
To sum values where the symbol is '÷' but strictly exclude any records marked as 'Discontinued', utilize the not-equal-to operator (`<>`). Type `=SUMIFS(C4:C7, D4:D7, "÷", E4:E7, "<>Discontinued")`.
Press the Enter key to execute the formula. The cell will now display the sum of values matching your specific inclusions and exclusions.
Calculate Complex Data Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SUMIFS, helping you analyze and process complex data with multiple criteria. It offers a user-friendly interface that lets you build advanced logical formulas with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your data workbook in the Spreadsheet module.
- 2. Select the target cell: Click the cell where you want the conditional sum result to appear.
- 3. Insert the SUMIFS formula: Type your `=SUMIFS` formula, utilizing inclusion (e.g., "Text") and exclusion (e.g., "<>Text") criteria rules as needed.
- 4. View your results: Press Enter to instantly view your calculated conditional sums and update them dynamically as your data changes.
Frequently Asked Questions
How do I exclude blank cells in a SUMIFS formula?
To exclude blank cells in your criteria range, use "<>" as the criteria. For example, `=SUMIFS(A2:A10, B2:B10, "<>")` will sum the values in column A only where the corresponding cells in column B are not empty.
Can I use wildcard characters with the excluded criteria in SUMIFS?
Yes, you can use wildcards like the asterisk (*) or question mark (?). To exclude any text containing the word 'Discontinued' anywhere in the cell, you would use "<>*Discontinued*" as your criteria.
Why is my SUMIFS formula returning a #VALUE! error?
A #VALUE! error in SUMIFS usually occurs when the sum range and the criteria ranges are not the same size. Double-check your formula to ensure all specified ranges have an identical number of rows and columns.
Can I use a cell reference instead of typing the excluded text?
Yes. To use a cell reference with an exclusion operator, you must concatenate the operator with the cell reference using an ampersand (&). For example, to exclude the value found in cell A1, format your criteria as `"<>"&A1`.
