Formula Errors

How to Exclude 99 from a SUMIFS Formula in Excel

Huda Qurayshi Sep 28, 2026 869 views

Question details

The user needs to calculate a sum based on conditions but wants to exclude a specific numeric value, such as 99, from the calculation using the SUMIFS function.

How to Exclude 99 from a SUMIFS Formula in Excel

Product: Excel
Device & OS: not provided
Scenario: Using the SUMIFS function to aggregate data based on conditions while actively filtering out specific numbers from the criteria range.
Observed behavior: The SUMIFS formula fails to correctly exclude the target value, often due to incorrect logical operator formatting, syntax errors with cell references, or data type mismatches.

Before you start

Before modifying your formula, ensure that the data range you are evaluating contains actual numbers and not numbers formatted as text, as this is a common reason for criteria failure in SUMIFS.

Solution 1Recommended

Use the Not Equal Operator with Hardcoded Criteria

Directly input the 'not equal to' operator into your SUMIFS formula to exclude the specific number from the calculation.

When the exclusion value is static, you can wrap the logical operator and the number within double quotation marks.

Select the target cell

Click on the cell where you want the final SUMIFS calculation result to appear.

Enter the SUMIFS formula

Start typing your formula and define your sum range and criteria range. For example: =SUMIFS(B2:B100, A2:A100,

Apply the exclusion criteria

Add the exclusion criteria using the not equal operator (<>). The complete argument should look like "<>99".

Execute the formula

Your final formula should look similar to =SUMIFS(B2:B100, A2:A100, "<>99"). Press Enter to calculate.

Use the Not Equal Operator with Hardcoded Criteria

Numeric Values: Using "<>99" works perfectly when the source values are truly numeric. If your data contains text strings, this method may not evaluate correctly.

Solution 2

Exclude a Value Using a Dynamic Cell Reference

Combine the 'not equal to' operator with a cell reference using an ampersand if the exclusion value changes frequently.

Solution 3

Verify Data Types and Range Boundaries

If the formula still includes the number 99, troubleshoot the data types and ensure you are using bounded ranges instead of entire columns.

Master Complex Formulas in WPS Spreadsheet

Effortlessly Calculate and Exclude Data with WPS Office

WPS Spreadsheet offers powerful, user-friendly tools to manage your data. With complete support for functions like SUMIFS, you can seamlessly establish complex criteria, dynamically exclude specific values, and calculate your totals precisely.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you wish to aggregate.
2. Insert the SUMIFS Function: Select your target cell, navigate to the Formulas tab, and choose Insert Function to select SUMIFS.
3. Define Ranges and Criteria: Input your sum range and criteria range, then enter "<>99" or "<>"&A12 to exclude the value.
4. Apply and Analyze: Press Enter to execute the formula and instantly view your accurately filtered dataset.

100% compatible with Microsoft Excel formulas, functions, and file formats.Easily build and audit advanced logical criteria like SUMIFS and COUNTIFS.Built-in error checking to instantly identify formatting and data type mismatches.A free, lightweight, and highly efficient solution for all data analysis tasks.

Frequently Asked Questions

Why is my SUMIFS formula ignoring the '<>99' criteria?

This commonly occurs when the numbers in your criteria range are formatted as text rather than numerical values, or if there are hidden spaces. Select the range and use the 'Text to Columns' feature to convert them to true numbers.

Can I exclude multiple values in a single SUMIFS formula?

Yes. You can exclude multiple values by adding additional criteria ranges and criteria arguments to the same formula. For example, to exclude 99 and 100, format your formula as: =SUMIFS(SumRange, CriteriaRange, "<>99", CriteriaRange, "<>100").

What is the purpose of the ampersand (&) in the formula '"<>"&A12'?

The ampersand is used as a concatenation operator. It joins the text string containing the 'not equal to' operator ("<>") with the dynamic value stored in cell A12, allowing SUMIFS to properly read and execute the combined criteria.

Excel & Data Problems