While working on an **excel **worksheet, you need to work with different functions. Sometimes, you need to combine the functions also to solve a problem. In excel, **IF **and **COUNTIF **are both very common functions to use. In this article, I will show you the use of **IF and COUNTIF together in Excel with five easy methods.** I will add the necessary pictures for your better understanding.

**Table of Contents**hide

## Download Practice Workbook

Please download the workbook to practice yourself.

## 5 Effective Ways to Use IF and COUNTIF Functions Together in Excel

Let’s consider the following dataset about the** Products of ABC Traders**. The dataset has two columns, **B** and **C **called **Year **and **Products**. The dataset ranges from **B4 **to **C10**. I will use this dataset to show the **IF **and **COUNTIF **functions together in excel with **5 **suitable methods.

### 1. Use a Combination of IF and COUNTIF Functions to Find Duplicates in a Column

This is the first method of this article. I will use a combination of **IF **and **COUNTIF **functions to find duplicates in a column. Let’s follow the steps of the procedure. I have added a new column here for the result.

**Steps:**

- First, select the
**D5**cell. - Then, copy the following formula in the selected cell.

`=IF(COUNTIF($C$5:$C$10,C5)>1,"Duplicate","")`

- After pressing
**enter**you will get the following result. - After that, copy down the formula from
**D5**to**D10**.

- As a result, you will find the following result.

**🔎**** How Does the Formula Work?**

**COUNTIF($C$5:$C$10,C5):**this part of the formula counts the value of**C5**in the range of**C5**to**C10****IF(COUNTIF($C$5:$C$10, C5)>1, “Duplicate”,””):**Now, the returned value from the**COUNTIF**formula checks the argument. If the returned value is greater than 1 then the**IF**function will return the “**Duplicate**” text. Otherwise, nothing will be returned.

**Read More: ****Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)**

### 2. Apply IF and COUNTIF Functions to Get Unique Data in a Column

This is the second method of this article. I will apply the **IF **and **COUNTIF **functions here to get unique data in a column. Let’s follow the procedure step by step.

**Steps:**

- Select the
**D5**cell first.

- After that, write down the following formula in the
**D5**cell.

`=IF(COUNTIF($C$5:$C$10,C5)=1,"Unique","")`

- After pressing
**enter**you will get the following result. **Fill handle**the formula from the**C5**to**C10**cells.

- Consequently, you will find the result just like the picture given below.

**🔎**** How Does the Formula Work?**

**COUNTIF($C$5:$C$10,C6):**this part of the formula counts the value of**C5**in the range of**C5**to**C10**.**IF(COUNTIF($C$5:$C$10, C5)=1, “Unique”,””):**Now, the returned value from the**COUNTIF**formula checks the argument. If the returned value is equal to**1**then the**IF**function will return “**Unique**” text. Otherwise, nothing will be returned.

**Read More: ****How to Use COUNTIF for Non Contiguous Range in Excel**

### 3. Insert IF and COUNTIF Functions Together to Find the First Occurrences of Data in a Column

This is the third unique method to use **IF **and **COUNTIF **functions together. I will insert **IF **and **COUNTIF **functions together to find the first occurrences of data in a column. Follow the following steps and have a look at the illustrations.

**Steps:**

- Select the
**D5**cell.

- Then, copy the following formula in the selected cell and press
**enter**.

`=IF(COUNTIF($C$5:C5,C5)=1,"First","")`

- Then, copy down the formula from
**D5**to**D10**cell.

- Hence, you will find the result just like the picture given below.

**🔎**** How Does the Formula Work?**

**COUNTIF($C$5:C5, C5)=1:**This part of the formula counts the value of**C5**in the range of**C5**to**C****10.****IF(COUNTIF($C$5:C5, C5)=1, “First”,””):**Now, the returned value from the**COUNTIF**formula checks the argument. If the returned value is equal to**1**then the**IF**function will return the “**First**” text. Otherwise, nothing will be returned.

**Read More:** **How to Use Nested IF and COUNTIF Formula in Excel**

**Similar Readings**

**VBA COUNTIF Function in Excel (6 Examples)****COUNTIF Excel Example (22 Examples)****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria****Excel COUNTIF Function with Conditional Formatting (7 Examples)**

### 4. Combining IF and COUNTIF Functions to Mark Data Existence

This is the fourth method to use **IF **and **COUNTIF **Functions together. Here I will use **IF **and **COUNTIF **Functions to mart data existence in a dataset. Follow the following steps and mark the data’s existence.

**Steps:**

- Select the
**C12**cell first.

- Then, write down the following formula in the selected cell.

`=IF(COUNTIF(C5:C10, "Apple")>0, "Exists", "Doesn't Exist")`

- After that, press the
**enter**button. - Hence, you will get the result like the picture given below.

**🔎**** How Does the Formula Work?**

**COUNTIF(C5:C10, “Apple”):**This part of the formula counts the value “**Apple**” in the range of**C5**to**C10**cell.**IF(COUNTIF(C5:C10, “Apple”)>0, “Exists”, “Doesn’t Exist “):**Now, the returned value from the**COUNTIF**formula checks the argument. If the returned value is greater than**0**then the**IF**function will return the “**Exists**” text. Otherwise, “**Doesn’t Exis**t” will be returned.

**Read More:** **Excel COUNTIF with Greater Than and Less Than Criteria**

### 5. Apply IF and COUNTIF Functions Together to Show Results Based on the Amount of Certain Data

This is the last but not the least method of this article. I will apply here **IF **and **COUNTIF **together to show results based on the amount of certain data. let’s follow the procedure step by step.

**Steps:**

- First, select the
**C12**cell.

- Then, copy the following formula
**C12**cell.

`=IF(COUNTIF(C5:C10, "Apple")=0, "0", IF(COUNTIF(C5:C10, "Apple")=1, "One", "Multiple"))`

- After pressing enter, you will find the result like the picture given below.

**🔎**** How Does the Formula Work?**

**COUNTIF(C5:C10, “Apple”):**This part of the formula counts the value “**Apple**” in the range of**C5**to**C10**cell.**IF(COUNTIF(C5:C10, “Apple”)=0, “0”, IF(COUNTIF(C5:C10, “Apple”)=1, “One”, “Multiple”)):**Now, the returned value from the**COUNTIF**formula checks the argument. If the returned value is equal to 0 then the IF function will return “**0**” text. Meanwhile in the next**COUNTIF**function, if the returned value is equal to 1 then the**IF**function will return “**One**” text Otherwise, “**Multiple**” will be returned.

**Read More:** **COUNTIF Function to Count Cells That Are Not Equal to Zero**

## Things to Remember

- As the fifth formula is a big one, you need to be careful when you give input on the values and arguments.

## Conclusion

In this article, I have tried to explain **how to use IF and COUNTIF together in Excel**. I hope, you have learned something new from this article. Now, extend your skill by following the steps of these methods. You will find such interesting blogs on our website **ExcelDemy.com**. I hope you have enjoyed the whole tutorial. If you have any queries, please ask me in the comment section. Don’t forget to give us your feedback.