Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)

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.

excel countif greater than 1

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.

Using COUNTIF Function for Single Column to find values greater than 1 in Excel

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.

Applying COUNTIF Function for Multiple Columns to find values greater than 1 in Excel

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.

Applying COUNTIF Function for Multiple Columns to find values greater than 1 in Excel

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


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.

Using Excel COUNTIF Function to Count Cells Greater Than 0

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.

Excel COUNTIF Function to find values Greater Than Cell Value

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.

Practice section to use the COUNTIF function to find values greater than 1


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

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo