How to Use IF and COUNTIF Functions Together in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

dataset of if and countif together


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.

find duplicates by if and countif together

Steps:

  • First, select the D5 cell.
  • Then, copy the following formula in the selected cell.
=IF(COUNTIF($C$5:$C$10,C5)>1,"Duplicate","")

find duplicates by if and countif together

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

find duplicates by if and countif together

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

find duplicates by if and countif together

🔎 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: How to Use COUNTIF and COUNTA Functions Together in Excel


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.

find unique data by if and countif together

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

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

find unique data by if and countif together

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

find unique data by if and countif together

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

find unique data by if and countif together

🔎 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 the Combination of COUNTIF and SUMIF 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.

find first data by if and countif together

  • Then, copy the following formula in the selected cell and press enter.
=IF(COUNTIF($C$5:C5,C5)=1,"First","")

find first data by if and countif together

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

find first data by if and countif together

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

find first data by if and countif together

🔎 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 C10.
  • 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 COUNTIF Function 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 Exist” will be returned.

Read More: Difference Between SUMIF and COUNTIF Functions in Excel


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: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel


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.


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Souptik Roy
Souptik Roy

Souptik Roy, a BSc graduate in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, dedicated 1.5 years to the ExcelDemy project. During this time, he authored 50+ articles and reviewed 20+ for ExcelDemy. Presently, he is a designer and content developer at YouHaveGotThisMath and Brainor, sister concerns of ExcelDemy. His educational content spans science, mathematics, and grammar. Roy's interests include developing creative ideas, visualizing concepts with tools like Adobe Illustrator, and problem-solving within Microsoft... Read Full Bio

2 Comments
  1. 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

    • Reply Avatar photo
      Saquib Ahmad Shuvo Jul 2, 2023 at 1:23 PM

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo