IF COUNTIF Functions and Usage in Excel

In this tutorial, you will learn completely about the IF COUNTIF formulas combination. There are several interesting things we can do with IF COUNTIF in our data processing process in excel.

IF is a formula that gives a result based on a logic condition while COUNTIF counts data that meet a criterion. Using them both in one writing can expand their usabilities greatly.

Want to know more? Read all parts of this tutorial below!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

General Writing and Inputs

Generally, the writing form of the IF COUNTIF combination in excel is as follows.



The inputs of IF COUNTIF are the same as if we use those formulas individually.

We need the cell range where we will count the data and the criterion for the counting process in COUNTIF. We also add the condition input after we write the COUNTIF to determine the evaluation we do to the COUNTIF result. As the COUNTIF result is a number, we most probably add a condition to evaluate a number there (equal to, more than, or less than).

Inside the IF, behind the COUNTIF logic condition, we input the result if the condition is TRUE or FALSE.

In this IF COUNTIF combination, we want to get a result based on the counting process result of COUNTIF. What are the ways we can utilize that when we process data in excel? See the five interesting ways of the utilization in the next parts of the tutorial.

Using IF COUNTIF to Find Duplicates in a Column

One way to utilize IF COUNTIF in excel is to make this formulas’ combination find duplicate data in our column.

How to do it? Here is the general writing form of the IF COUNTIF for that purpose.



We input the column cell range and the parallel row data in our COUNTIF. As we want to find duplicates, we input the condition of “more than 1 (>1)” after the COUNTIF.

If this is true, then that means the parallel row data we evaluate is a duplicate. Next, we just need to input the mark we want to give if the data is a duplicate and if it isn’t.

Got confused with the explanation? See the example below to make you understand the concept more.



In this example, we try to find the duplicates in the name column. We use the IF COUNTIF for that in a duplicates identification helper column.

In the COUNTIF, we input the cell range of the name column with dollar symbols ($) to copy the formula easier. We use the name data in the parallel cell to where we write the COUNTIF as the criterion.

As we discussed previously, the condition for the COUNTIF result is “more than 1” as the indicator of the duplicates. If the COUNTIF logic condition is TRUE, then we will produce a “Duplicate” text as a mark. If not, then we will produce empty data (which we can input as empty quotes in excel).

The result of that writing is as you can see in the screenshot! The duplicate ones are marked by IF COUNTIF as “duplicate” while the ones which are not aren’t marked. For the example case, we can see that the duplicate names are “James”, “Jones”, and “Chyntia”.

Using IF COUNTIF to Find Unique Data in a Column

What if we want to find unique data instead of duplicates? It is an easy thing if we have mastered the way to find duplicates using IF COUNTIF!

As unique data is the opposite of a duplicate, we can just tweak the IF COUNTIF writing for duplicates a bit. Instead of using the “more than 1” criterion, we use the “equal to 1 (=1)” criterion (if the data is unique, then there should be only one of it in its column).



Here is the IF COUNTIF general writing form to find unique data in a column.



And here is the example of the IF COUNTIF implementation in excel.



We use the condition of “equal to 1” to find the unique data and we mark them with the “Unique” word. As a result, we can identify the unique names in the name column quickly (Natalia, Gilbert, Maria, Nina, Jonathan, Sherly, Anna, and Timmy)!

Using IF COUNTIF to Find the First Occurrences of Data in a Column

We can also combine IF and COUNTIF to find the first occurrences of data. To do that, we just need to adjust the inputs in our IF COUNTIF writing.

Crucially, we need to make the COUNTIF cell range input expand itself when we copy the formula down. This is so it can mark our data when it first shows up in our column.

Here is the general IF COUNTIF writing form to find the first occurrences of data in a column.



To make the column range expand itself, we will put dollar symbols in the first cell coordinate of the range (the cell coordinate that indicates the first cell included in the cell range). For the second cell coordinate of the range (the cell coordinate that indicates the last cell included in the cell range), we make it parallel with where we write our IF COUNTIF.

The dollar symbols will make that first cell coordinate not move when we copy the formula. The only one that moves in the COUNTIF cell range is the second cell coordinate. Because of that, the COUNTIF cell range will expand along with the direction of our formula copy process!

To make the understanding of the concept easier, here is an example of this IF COUNTIF usage in excel.



In the example, we try to find the first occurrence of each name in the name column.

In the COUNTIF writing for that purpose, we input the name column cell range with the way as discussed previously. We add dollar symbols for its first cell coordinate while making its second cell coordinate parallel with the COUNTIF writing location.

The result of this is a name column cell range input that can expand along when we copy our formula.

For the COUNTIF criterion, we input the name data which is parallel with the COUNTIF writing location. We use the “equal to 1” condition for the COUNTIF result to get the first occurrence of the name data.

For the rest of the IF, we input the formula result if the data is the first occurrence or if it isn’t.

After we write the IF COUNTIF for the first name, we copy the formula for the other names in the column. After the copy process, we will get the identification of the first occurrences of the names in the helper column!

Do that for your column data in excel as well and you will get the same result!

Using IF COUNTIF to Mark the Existence of Data

To know the existence of data in your cell range, you can use COUNTIF with the criterion of that data. As COUNTIF counts the amount of the data in the cell range, you will know whether the data exists or not. From there, you just need to use IF to give a mark to the result with something you prefer.



The general writing form of the IF COUNTIF to check the existence of data is as follows.



We use the condition of “more than zero (>0)” there as COUNTIF should produce that result if the data exists.

If it is TRUE, then we will mark the data with something that says the data exists in the cell range. If FALSE, then we will give the opposite mark.

For the example of this IF COUNTIF implementation in excel, take a look at the screenshot below.



In this example, we want to know whether the name “John” exists in the name column that we have. For that purpose, we use IF COUNTIF to automatically do the checking process.

As you can see there, we input the name column cell range and “John” as the criterion in our COUNTIF. This makes our COUNTIF count the occurrences of “John” in the name column.

If the COUNTIF result is more than 0, then that means “John” is there and exists in the cell range. If it is 0, then that means “John” isn’t there and doesn’t exist in the cell range.

We input the words “Exists” and “Doesn’t Exist” in our IF inputs. These words mark the result of the data existence checking using IF COUNTIF.

As a result of our formula writing, we now know that “John” doesn’t exist in the name column!

Using IF COUNTIF to Give a Result Based on the Amount of Certain Data

This one should be easy if you have followed the tutorial until this point, right? As COUNTIF counts the amount of certain data, we just need to make it the logic condition input in our IF. And because of that, our IF can give a result based on the COUNTIF counting result!

The general writing form of IF COUNTIF for this purpose is as follows.



Those triple dots ( … ) before the false result input represents additional IF COUNTIF writings if you need them. You should write the number of IF COUNTIFs according to the data amount criteria that you have. You should also input the conditions to evaluate your COUNTIF results according to the criteria that you have (must be number-related conditions, of course).

Here is an example of the concept implementation in excel.



Here, we want to get a result based on the number of “James” names we have in the name column. For that, we use nested IF COUNTIFs to make us get the result easier.

We input the name column cell range for each COUNTIF in the writing. We also use “James” as the criterion of the COUNTIF counting process.

From there, we input the logic conditions we want for each of the COUNTIF results. If it is 0, then we have the “No James” mark. If 1, then it is “One James”. Otherwise, it is the “Multiple James” mark.

Write our IF COUNTIFs like that and we will get the result we want!

Exercise

After we have learned how to use IF COUNTIF in excel completely, now let’s do a related exercise. This is so you can deepen your understanding of using IF COUNTIF!

Download the exercise file and answer the questions below! Download the answer key file if you have done the exercise and want to check your answers. Or probably when you don’t know the answers to the questions!

Link to the exercise file:
Download here

Questions

The exercise file contains transaction data in a day for an electronic shop. Answer all the questions with IF COUNTIF!

1. What are the products sold more than once that day? Mark them with the word “Multiple”. If not sold more than once, then mark the products with “Non-Multiple”. Answer this question in the “Multiple?” column!
2. What are the products sold just once that day? Mark them with the word “Once”. If not sold just once, then mark the product with “Not Once”. Answer this question in the “Once?” column!
3. In which hour each product is sold the first time on that day? Mark the transaction hour with “First” or empty the cell if the transaction hour isn’t the first for the product. Answer this question in the “First?” column!
4. Is there a radio transacted that day? Answer with “Yes” or “No” in the appropriate cell!
5. How many dispensers are sold that day? Answer with “None” if 0, “Just One” if 1, and “Many” if more than 1 in the appropriate cell!

Link to the answer key file:
Download here

Additional Note

You can have up to 64 IF COUNTIF if you use multiple IF COUNTIF statements in one writing.



Other tutorials you should learn: