COUNTIF with Multiple Criteria in Different Columns in Excel (Both Single and Multiple Criteria)

Today I will be showing how you can count cells in different columns with multiple criteria in Microsoft Excel using the COUNTIF function.

Download Practice Workbook

COUNTIF with Multiple Criteria in Different Columns in Excel

Let us have a look at this data set. We have the sales record of a company named Martino Garments.

We have the item names, quantity produced, and price per piece item in columns A, B, and C respectively.

Now let us try to count the number of cells maintaining different criteria.

Case 1: Maintaining a Single Criteria

i. Criteria Equal to a Given Value

Let us try to find out the number of items with prices equal to $200. We shall use the COUNTIF() function here.

Syntax

• Takes two arguments, one range of cells and a specific criteria.
• Returns the number of cells within that range which maintains the specific criteria.

As we want to know how many cells are there in column D with prices equal to 200, our formula will be

We see, there are 4 items with a price equal to $200.

ii. Criteria Greater Than or Less Than a Given Value

Now consider another criterion. We have to find out the total number of items with a price greater than $100.

The formula here will be

Note: When the criteria have a sign along with a value ( In this case, greater than (>)), then it must be enclosed within an apostrophe (“”).

See, there are 8 items with prices greater than $100.

Do the same for Less Than.

iii. Criteria with a Sign and a Cell Reference

Now let us try a different thing. In cell G4, we have $100.00

Now if one asks to know the total number of items with prices greater than G4, then?

Note: If you have a sign and a cell reference together in the criteria of COUNTIF() function, you have to use the sign between apostrophe (“”) with the cell reference concatenate with the Ampersand (&) symbol.

So the formula here will be

See, we have 4 items with a price greater than G4, that is,  $100.

iv. Counting the Number of Cells with Text

If you want to count the number of cells having strings in a cell range, you can use COUNTIF() function with the Wildcard(*) symbol.

For example, to find out the total number of items in column B, the formula will be

See, the total number of items is 14.

Case 2: Maintaining Multiple Criteria

i. Multiple Criteria of OR Type

Method 1: Using Two COUNTIF() Functions

Let us try to find out how many items have prices greater than $100 or quantities produced greater than 1000.

We can use two COUNTIF() functions together to satisfy the two conditions of our problem..

The formula will be:

See, we have 17 items with prices greater than $100 or quantities produced greater than 1000.

Note: If you have multiple criteria of the same column, the process is the same.

For example, to find out the number of items with prices less than $100 or greater than $200, the formula will be

Method 2: Using SUMPRODUCT() Function

i. Multiple Criteria of Different Columns

If you have multiple criteria of OR type, and of different columns, you can use two SUMPRODUCT() functions to find that.

For example, to find out the number of items with prices greater than $100 or quantity greater than 1000, the formula will be

See, we have 17 items with prices greater than $100 or quantities produced greater than 1000.

The same result as earlier.

ii. Multiple Criteria of the Same Column

If you have multiple criteria of the same column, for example, to find out the number of items with a price less than $100 or greater than $200, you can either use the same type formula of two SUMPRODUCT() functions

Or you can use a slightly different yet concise formula:

See, we have 6  items with prices less than $100 or greater than 200.

Read More: COUNTIF with Multiple Criteria in Different Columns in Excel

ii. Multiple Criteria of AND Type

Now let us try another different thing.

Let us try to find out how many items are there with prices greater than $100 and quantities greater than 1000.

You can find it out using these two ways.

Method 1: Using COUNTIFS() Function

Syntax

• Takes one or more ranges of cells and corresponding criteria.
• Returns the total number of cells that maintain all the criteria.

Our formula here will be

See, we have 4 items with prices greater than $100 and quantity produced greater than 1000.

Note: If you have multiple criteria of AND type, but of the same column, the process is the same.

For example, to find out the number of items with price greater than $100 and less than $200, the formula will be

Method 2: Using SUMPRODUCT() Function

This time we will again find out the number of items with a price greater than 100 and quantity greater than 1000, but with SUMPRODUCT() function.

The formula will be

See, we have got the same result,4.

Note: For multiple criteria of the same column, the process is the same.

For example, to find out the number of items with prices greater than $100 and less than $200, the formula will be

Conclusion

Using these methods, you can easily count the number of cells that maintain single or multiple criteria in Excel. Do you know any other method? Let us know in the comment section.

Further Readings

• How to Apply COUNTIF Not Equal to Text or Blank in Excel