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**

`=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)`

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

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

**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

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

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

`=COUNTIF(B4:B17,"*")`

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:

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

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

`=COUNTIF(D4:D17,"<100")+COUNTIF(D4:D17,">200")`

**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

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

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

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

Or you can use a slightly different yet concise formula:

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

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

**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**

`=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")`

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")`

**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

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

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 single or multiple criteria in Excel. Do you know any other method? Let us know in the comment section.