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

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

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

**Similar Readings**

**How to Use Nested COUNTIF Function in Excel****How to Use COUNTIF Function In Excel to Count Bold Cells****How to Use COUNTIF Function with Array Criteria in Excel****How to Use the Combination of COUNTIF and SUMIF in Excel****How to Use COUNTIF and COUNTA Functions Together in Excel**

### 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 Function with Conditional Formatting**

### 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.

**Download Practice Workbook**

Please download the workbook to practice yourself.

## 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. 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.

## Related Articles

**Excel COUNTIF to Count Cell That Contains Text from Another Cell****How to Use COUNTIF Function to Count Text from List in Excel****Count Text at Start with COUNTIF & LEFT Functions in Excel****How to Compare Two Columns Using COUNTIF Function****How to Use Excel COUNTIF Between Time Range****How to Use COUNTIF to Count Date Less Than Today in Excel****How to Use COUNTIF Function to Calculate Percentage in Excel****How to Use COUNTIF Function in Excel Greater Than Percentage****How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel****Difference Between SUMIF and COUNTIF Functions in Excel****How to Calculate Frequency Using COUNTIF Function in Excel****[Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value****[Fixed] COUNTIF Function with Wildcard Not Working in Excel****[Fixed!] Excel COUNTIF Function Not Working for String “True”**

is something like this possible?

IF(COUNTIFS(R2,”value1*”,S2,”status1″),”aaa”,”bbb”),IF(COUNTIF(R6,”value2*”),”aaa”,”bbb”)

or

IF(COUNTIFS(R2,”value1*” OR “value2″,S2,”status1” OR “status2” or “status3″),”aaa”,”bbb”)

Thanks

Dear EMMA PALMER,

Greetings. Thank you for your question. I have provided a primary solution to your question. It would be much easier for me to solve your problem if you could send me your dataset and Excel workbook.

Yes, it is feasible to use both of the formulas you gave. However, your usage of syntax is not totally accurate. The proper syntax for each formula is as follows:

Formula 1:

=IF(COUNTIFS(R2,”value1*”,S2,”status1″),”aaa”,”bbb”)This formula checks if the value in cell R2 starts with “value1” and the value in cell S2 is “status1”. If both conditions are true, it returns “aaa”; otherwise, it returns “bbb”.

Formula 2:

=IF(OR(COUNTIF(R2,”value1*”), COUNTIF(R2,”value2*”)), “aaa”, “bbb”)This formula checks if the value in cell R2 starts with either “value1” or “value2”. If either condition is true, it returns “aaa”; otherwise, it returns “bbb”.