Microsoft Excel has provided very useful & common functions named **COUNTIF** & **COUNTIFS** to count cells under multiple criteria in different columns or rows. In this article, we’ll try to illustrate how we can use these** two** fruitful functions to count cells with multiple criteria from different columns.

## Download Practice Workbook

You can download our Excel Workbook that we’ve used to prepare this article. You can input or change data to find different results under embedded formulas in the resultant cells.

## 5 Uses of COUNTIF Function with Multiple Criteria in Different Columns in Excel

Before getting down to the uses of the **COUNTIF **function, let’s have a look at the introductions of this function.

**Formula Syntax:**

**=COUNTIF(range, criteria)**

**Arguments of the Formula:**

Counts the number of cells within a range that meet the given conditions.

**Arguments:**

**range–** A range of cells along columns or rows or both that needs to be counted.

**criteria–** Given conditions under which the function will work to count cells.

Furthermore, **COUNTIFS** is a sub-category of** the COUNTIF** function & it’s capable of incorporating more than** one **criterion.

Well, now we can start on the uses of **COUNTIF **as well as **COUNTIFS** functions with relatable datasets.

### 1. Incorporating Multiple COUNTIF Functions with Multiple OR Criteria in Distinct Columns

Here’s a dataset where we’ll apply multiple **COUNTIF** functions to count data under **different criteria in different columns**.

Basically, there are **3 slots** in our dataset. All of them have lists of distinct names & IDs. We have to count only names from **Slot A**, only IDs from **Slot B** & all-blank cells from **Slot C**. Then we’ll make a sum of all counts under these categories.

📌 **Steps:**

- Firstly, in Cell
**F12**, type-

`=COUNTIF(B5:B19,"*")+COUNTIF(C5:C19,">0")+ COUNTIF(D5:D19,"<>"&"")`

Here, the **COUNTIF** functions here have been used for all **3 slots** separately. Then we simply added all these counts by inserting Plus **(‘+’)** in the function bar.

- Subsequently, press
**ENTER.**

As a result, you’ll notice that total of **27 **counts have been found from **3 **columns under different criteria.

**Read More:** **How to Apply SUM and COUNTIF for Multiple Criteria in Excel**

### 2. Use of COUNTIFS Function with Multiple Criteria in Different Columns

We have now another dataset where sales units of different brand devices in** 3 months **are stored. Now, we want to find a specific count under multiple criteria. If we want to count the number of **Lenovo notebooks** with **more than 40 sales **in those **3 ****months,** then we can use the **COUNTIFS** function by combining multiple criteria.

📌** Steps:**

- Firstly, select Cell
**G13**& type-

`=COUNTIFS($C$5:$C$19,"Notebook",$D$5:$D$19,"Lenovo",$E$5:$E$19,">40")`

- After that, press
**ENTER**.

Lastly, you’ll see only **2 instances** of** more than 40 sales** of** the Lenovo Notebook**. So, this formula is effective enough when you have to count specific data under multiple criteria from a large number of cells or a large table.

**Read More: How to Use COUNTIFS to Count Across Multiple Columns in Excel**

### 3. Combination of COUNTIFS & SUM Functions in Separate Columns

Here, we want to know the number of** sales of more than 40 **for both device categories of the **Lenovo** brand. In addition, we’re going to use the combination of **COUNTIFS**, and **SUM** functions.

📌 **Steps:**

- Firstly, go to Cell
**G13**& type-

`=SUM(COUNTIFS($C$5:$C$19,{"Notebook","Desktop"},$D$5:$D$19, "Lenovo",$E$5:$E$19,">40"))`

- Subsequently, press
**ENTER**& you’re done.

So, now you’re seeing the** Total Counts- 4 **as there are **4 instances **of** Lenovo** devices that have been sold** more than 40 units **over** 3** specific months in a year.

**Read More:** **How to Use COUNTIF with Multiple Criteria in the Same Column in Excel**

### 4. Use of AND and COUNTIF Functions for Applying Multiple Criteria

Here, we will use **AND**, and **COUNTIF** functions for multiple criteria in different columns in Excel. Suppose we want to find a specific count under multiple criteria. Additionally, let’s count the number of **Lenovo notebooks** with **more than 40 sales **in **3 months**. The steps are given below.

**📌**** Steps:**

- Firstly, you have to select a new cell
**F5**where you want to keep the**Status**. - Secondly, you should use the formula given below in the
**F5**cell.

`=AND(C5= "Notebook",D5= "Lenovo",E5>40)`

Here, in this formula, the **AND** function will return **TRUE **if the cell value of **C5** is **“Notebook”, **the cell value of** D5** is **“Lenovo”**, and the cell value of **E5** is greater than** 40**.

- Thirdly, press
**ENTER**to get the result.

- After that, you have to drag the
**Fill Handle**icon to**AutoFill**the corresponding data in the rest of the cells**F6:F19**.*Or you can double-click on the***Fill Handle**icon.

Finally, you will get the** Status**. This means you will come to know whose cells fulfill that logic.

- Then, write down the following formula in the
**H13**cell.

`=COUNTIF(F5:F19,TRUE)`

Here, in this formula the **COUNTIF** function will count those cells which contain **TRUE** as a value.

- After that, press
**ENTER**.

Finally, you will get the **2 instances** for** more than 40 sales** of** Lenovo Notebook**.

**Read More:** **COUNTIF Between Two Values with Multiple Criteria in Excel**

### 5. Applying COUNIF Function as Array

Here, we will use the **COUNTIF** function as an array for multiple criteria in different columns in Excel. Suppose, we want to count the total number of **Lenovo notebooks** with **more than 40 sales **in the **3 months. **The steps are given below.

**📌**** Steps:**

- Firstly, select a new cell
**H8**where you want to keep the result.*Here, you should keep***blank cells**next to the**H8***cell (vertically). Furthermore, the***blank cells**should be equal to the number of given criteria. - Secondly, you should use the formula given below in the
**H8**cell.

`=COUNTIF(C5:E19,G8:G10)`

Here, the **COUNTIF** function will count cells from the **C5:E19 **data range, which will fulfill the given condition. Additionally, **G8:G10** is the criteria range.

- Subsequently, press
**ENTER**to get the result.

Finally, you will get the **total counts** for individual criteria.

**Read More:** **How to Apply COUNTIF Not Equal to Text or Blank in Excel**

## How to Use SUMPRODUCT Function to Count Cells with Multiple Criteria in Different Columns in Excel

Let’s go back to our** 2nd **dataset where we had to find the total counts for** more than 40 sales** of **Lenovo Notebooks **over **3 **months. Here, we can get a similar result by applying the **SUMPRODUCT function** too.

Now, let’s see the steps.

**📌**** Steps:**

- Firstly, select Cell
**G13**& type-

`=SUMPRODUCT((C5:C19=C5)*(D5:D19=D5)*(E5:E19>40))`

- After that, press
**ENTER**& you’ll find the similar number of counts that we’ve got previously by using the**COUNTIFS**function.

While counting instances under multiple criteria in different columns, the difference between **COUNTIFS** & **SUMPRODUCT** functions is that you have to use** Commas (,) **to add multiple criteria in the **COUNTIFS** function but in the **SUMPRODUCT** function, you have to use **Asterisks (*)** to assign a similar role.

**Read More:** **SUMPRODUCT and COUNTIF Functions with Multiple Criteria**

## Practice Section

Now, you can practice the explained method by yourself.

## Concluding Words

We hope all these simple methods under multiple criteria in different columns, we’ve described, will help you to understand when & how to use the **COUNTIF** along with **COUNTIFS** functions properly in your own datasets. Now, if you think we’ve missed a point or a method we should’ve put in then please let us know in the comment section. Then, we’ll update the article soon following your valuable recommendations.

**Related Articles**

**Apply COUNTIF Function in Multiple Ranges for Same Criteria****INDEX, MATCH, and COUNTIF Functions with Multiple Criteria****How to Use COUNTIF Function Across Multiple Sheets in Excel****Excel COUNTIF Function with Multiple Criteria & Date Range****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria****How to Use COUNTIF Between Two Dates and Matching Criteria in Excel**

I need count one criteria in (A1:A10,”TOM”) & Count how many in C1:C10

COLUMN A feild “NAME”

COLUMN C Field in No of “BL” ANSWER= 5

A C

TOM 2

ANN 1

TOM 1

ABI 2

ANN 2

TOM 2

Thank you ZOYSA for your comment. Below, I have attached two formulas for your problem.

I have written the data from the A2 cell and C2 cell. According to your question, I have considered the dataset till A10 and C10.

Firstly, write the below formula in the E2 cell or the cell from where the NAME will be started.

=IF(A2=”TOM”,C2,””)Then copy this formula up to E10 or your dataset’s end cell.

Then use another formula in the F4 cell.

=SUM(E2:E10)