Zahid
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.
Download Practice Workbook
2 Simple Methods to Use COUNTIF Function to Count Cells 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: How to Use COUNTIF for Non Contiguous Range in Excel
2. Computing 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: Excel COUNTIF to Count Cell That Contains Text from Another Cell
Similar Readings
- How to Compare Two Columns Using COUNTIF Function (4 Ways)
- Count Text at Start with COUNTIF & LEFT Functions in Excel
- How to Use Excel COUNTIF Between Time Range (2 Examples)
- [Fixed] COUNTIF Function with Wildcard Not Working in Excel
- How to Use COUNTIF Function In Excel to Count Bold Cells
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: Excel COUNTIF Function with Conditional Formatting (7 Examples)
Applying 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.
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: COUNTIF Date Is within 7 Days
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.
Related Articles
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use IF and COUNTIF Functions Together in Excel
- COUNTIF Excel Example (22 Examples)
- How to Use COUNTIF Between Two Numbers (4 Methods)
- Excel COUNTIF with Greater Than and Less Than Criteria
- How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
