COUNTIF Function in Different Columns with Multiple Criteria in Excel

Two COUNTIF Functions in Excel

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

Download Practice Workbook

How to Use COUNTIF() Function in Different Columns with Multiple Criteria 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 column A, B and C respectively.

A Data set in Excel

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

1. Maintaining a Single Criteria

1.1 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

=COUNTIF(range, criteria)
  • 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

=COUNTIF(D4:D17,200)

COUNTIF Function in Excel

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

1.2 Criteria Greater Than or Less Than a Given Value

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

The formula here will be

=COUNTIF(D4:D17,">100")

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

COUNTIF Function in Excel with apostrophe

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

Do the same for Less Than.

1.3 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

=COUNTIF(D4:D17,">"&G4)

COUNTIF Function in Excel with cell reference in criteria

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

1.4 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

=COUNTIF(B4:B17,"*")

COUNTIF Function with wildcard

See, the total number of items is 14.

2. Maintaining Multiple Criteria

2.1 Multiple Criteria of OR Type

2.1.1 Using Two COUNTIF() Functions

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

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

The formula will be

=COUNTIF(D4:D17,">100")+COUNTIF(C4:C17,">1000")

Two COUNTIF Functions in Excel

See, we have 17 items with prices greater than $100 or quantity 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 price less than $100 or greater than $200, the formula will be

=COUNTIF(D4:D17,"<100")+COUNTIF(D4:D17,">200")
2.1.2 Using SUMPRODUCT() Function
2.1.2.1 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

=SUMPRODUCT(--((D4:D17)>100))+SUMPRODUCT(--((C4:C17)>1000))

Two SUMPRODUCT() functions in Excel

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

The same result as earlier.

2.1.2.2 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 price less than $100 or greater than $200, you can either use the same type formula of two SUMPRODUCT() functions

=SUMPRODUCT(--((D4:D17)<100))+SUMPRODUCT(--((D4:D17)>200))

Or you can use a slightly different yet concise formula:

=SUMPRODUCT(COUNTIF(D4:D17,{"<100",">200"}))

SUMPRODUCT and COUNTIF function in Excel

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

2.2 Multiple Criteria of AND Type

Now let us try another different thing.

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

You can find it out using these two ways.

2.2.1 Using COUNTIFS() Function

Syntax

=COUNTIFS(range1, criteria1, [range2], [criteria2],...)
  • 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

=COUNTIFS(D4:D17,">100",C4:C17,">1000")

COUNTIFS function in Excel

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

=COUNTIFS(D4:D17,">100",D4:D17,"<200")
2.2.2 Using SUMPRODUCT() Function

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

The formula will be

=SUMPRODUCT(((D4:D17)>100)*((C4:C17)>1000))

SUMPRODUCT() function in Excel

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

=SUMPRODUCT(((D4:D17)>100)*((D4:D17)<200))

Conclusion

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

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo