In Excel, sometimes we need to find the values that are greater than 1. If we work with a small dataset, we might be able to do it manually. But for a large dataset, it’s more logical to use any of the features that Excel provides. Among them, using the COUNTIF function of Excel allows us to find values greater than 1 quite efficiently. In this article, we will learn two simple methods to use the COUNTIF function to find values greater than 1.
In this section of the article, we will learn two simple methods to use the COUNTIF function of Excel to find values greater than 1. Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.
1. Counting Cells Greater Than 1 for Single Column
The COUNTIF function can be used efficiently for single columns. Let’s say, we have the Sales Status of ABC Store as our dataset. Our goal is to find the number of Items that are sold more than 1 by using the COUNTIF function. Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the following formula in cell D5.
=COUNTIF(C5:C11,">"&1)Here, the range of cells C5:C11 indicates the cells of the Sold Quantity column.
- After that, press ENTER.
Consequently, you will have the number of Items that are sold more than 1 unit in cell D5.
Read More: COUNTIF Between Two Cell Values in Excel
2. Computing the Number of Cells Greater Than 1 for Multiple Columns
We can also apply the COUNTIF function to multiple columns without difficulty. For instance, we have the Daily Sales Data of ABC Store as our dataset. Our goal is to calculate the number of Items that are sold in more than 1 unit. Let’s use the instructions outlined below to do this.
Steps:
- Firstly, apply the formula given below in cell G6.
=COUNTIF(C6:F12,">"&1)Here, the range of cells C6:F12 represents the cells of the Sold Quantity column.
- After that, hit ENTER.
As a result, you will have the following output on your worksheet, as demonstrated in the following image.
Read More: How to Use COUNTIF for Non Contiguous Range in Excel
Using Excel COUNTIF Function to Count Cells Greater Than 0
Also, you can utilize the COUNTIF function to find values greater than 0 by following some simple steps. Let’s follow the procedure discussed in the following section to find values greater than 0 using the COUNTIF function in Excel.
Steps:
- Firstly, use the following formula in cell D5.
=COUNTIF(C5:C11,">"&0)- Then, press ENTER.
That’s it! Now, you will be able to see the following output in cell D5, as shown in the picture below.
Read More: How to Use COUNTIF Function in Excel Greater Than Percentage
Applying the Excel COUNTIF Function to Count Cells Greater Than a Specific Cell Value
In this section of the article, we will learn how we can use the COUNTIF function to find values that are greater than a specific cell value. The cells can contain any number format. Let’s follow the instructions mentioned below to do this.
Steps:
- Firstly, use the following formula in cell D5.
=COUNTIF(C5:C11,">"&C13)Here, cell C13 indicates the amount of Minimum Sales Qty.
- After that, press ENTER.
Subsequently, you will have the count of cells that are greater than a specific cell (in this case, it is cell C13) in cell D5.
Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero
Utilizing Excel COUNTIF Function to Count Cells Greater Than Reference Date
In Excel, we use the COUNTIF function to count a number of cells by applying certain conditions. Now, we will use greater than a date as criteria in the COUNTIF function. Let’s say, we have the Joining Information of Employees of a company. Our goal is to find the Number of Employees that joined after 1-Jan-22. Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, apply the following formula in cell D5.
=COUNTIF(C5:C11,">"&C13)Here, the range of cells C5:C11 indicates the cells of the Joining Date column, and cell C13 represents the Reference Date.
- Now, hit ENTER.
Consequently, you will have the Number of Employees who joined after 1-Jan-22 in cell D5 as demonstrated in the following picture.
Read More: Â How to Use COUNTIF to Count Date Less Than Today in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
Download Practice Workbook
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to use the COUNTIF function of Excel to find values greater than 1. If you have any questions, suggestions, or feedback related to this article you can comment below.
Related Articles
- How to Use COUNTIF Function to Calculate Percentage in Excel
- How to Use COUNTIF Function with Array Criteria in Excel
- How to Calculate Frequency Using COUNTIF Function in Excel
- Excel COUNTIF Function with Conditional Formatting
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
