How to use COUNTIFS Function With Not Equal To Condition In MS Excel


COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

The COUNTIFS function uses the following arguments:

Criteria_range1 (required argument) – This is the first range that would be evaluated with the associated criteria.

Criteria1 (required argument) – The conditions to be tested against the values. The criteria can be in the form of a number, expression, cell reference, or text that define which cells shall be counted. For example, criteria can be expressed as 2, “>2,” A4, “Any Text,” or “32.”

Criteria_range2, criteria2, … (optional argument) – Additional ranges and their associated criteria. The function allows up to 127 range/criteria pairs.

Note: The given criteria can be any of the following:

  • It is a numeric value. It can be an integer, decimal, date, time, or logical value.
  • A text string. It may include wildcards. Wildcards can be a ? (question mark) or an * (asterisk). A ? matches any single character, whereas, * matches any sequence of characters.

Now, let’s try to learn it with an example.

We have sales data of a Men’s wear store above & we want to count the number of cells in the Month of February in which Products other than “Jacket” were sold. i.e exclude Jackets from the sale of February & show the count of remaining products.

There are times when we need to count cells based on a condition. In some cases, we may need to count cells whose values don’t equal the specified value. Excel has a simple solution for counting cells not equal to a certain value. Using the COUNTIF function, we can easily solve this problem. We will learn how to count cells in Excel that do not equal to another value in this article.

Step 1 – Select a Blank Cell

– Select a Blank Cell

Step 2 – Place Equals To Sign

– Start your formula with equals to sign in the targeted cell.

Step 3 – Type Formula

– Type COUNTIFS formula.

Step 4 – Get The Desired Result

– You can use COUNTIFS function with multiple criteria to get your desired result thru this way.