While working in Excel, we often need to count the number of cells irrespective of the data type of the cell contents based on some specific criteria. For small datasets, we might be able to do this manually. But for large datasets, counting cells manually becomes no longer a viable option. By using the COUNTIF and COUNTA functions together, we can do this quite easily. In this article, we will discuss 2 appropriate examples to use the COUNTIF and COUNTA functions together in Excel.
How to Use COUNTIF and COUNTA Functions Together in Excel: 2 Appropriate Examples
In this section of the article, we will discuss 2 useful examples to use COUNTIF and COUNTA functions together. For instance, let’s say we have Product list of a store as our dataset. Our aim is to count the cells in the Product column using the COUNTA and COUNTIF functions together.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
Example 01: Counting Cells While Excluding Some Specified Cells
In the first example, we will count the cells in the Product column while excluding some specified cells that are mentioned in the Excluded Product column. Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, use the following formula in cell C16.
=COUNTA(B5:B14)-SUM(COUNTIF(B5:B14,C5:C8))Here, the range of cells B5:B14 indicates the cells of the Product column, and the range of cells C5:C8 refers to the cells of the Excluded Product column.
Formula Breakdown
- COUNTIF(B5:B14,C5:C8)
- Here, B5:B14 → It is the range argument.
- C5:C8 → This refers to the criteria argument.
- Output →{1;1;1;1}.
- SUM(COUNTIF(B5:B14,C5:C8)) → It becomes SUM({1;1;1;1}).
- Here, the SUM function will return the sum of the {1;1;1;1} array.
- Output → 4.
- COUNTA(B5:B14)-SUM(COUNTIF(B5:B14,C5:C8)) → It becomes COUNTA(B5:B14)-4.
- Here, B5:B14 → It is the value1 argument.
- Output → 6.
- After that, press ENTER.
As a result, you will have the count of the cells of the Product column excluding the cells of the Excluded Product column in cell C16.
Read More: Difference Between SUMIF and COUNTIF Functions in Excel
Example 02: Counting Cells Without Null Value and 0 Value
Now, we will use the COUNTA and COUNTIF functions to count cells without a null value and 0 value.
Here, in cell C7, we used the following formula to get a null value as output.
=IF(2>1,"",0)And in cells C10 and C12, we have two 0 values.
Now, let’s use the instructions outlined below.
Steps:
- Firstly, apply the formula given below in cell C16.
=COUNTA(B5:B14)-COUNTBLANK(B5:B14)-COUNTIF(B5:B14,"=0")Here, the range of cells B5:B14 represents the cells of the Product column.
Formula Breakdown
- COUNTIF(B5:B14,”=0″)
- Here, B5:B14 → It is the range argument.
- “=0” → This refers to the criteria argument.
- Output → 2.
- COUNTBLANK(B5:B14) → The COUNTBLANK function will return the number of blank cells in the range B5:B14.
- Output → 1.
- COUNTA(B5:B14) → It returns the count of populated cells of the Product column.
- Output → 10.
- Now, COUNTA(B5:B14)-COUNTBLANK(B5:B14)-COUNTIF(B5:B14,”=0″) → It becomes 10-1-2.
- Output → 7.
- Then, hit ENTER from your keyboard.
Consequently, you will get the following output, as demonstrated in the following image.
Read More: How to Use the Combination of COUNTIF and SUMIF in Excel
COUNTA Vs COUNTIF Function in Excel
COUNTA and COUNTIF functions are one of the most used functions of Excel. In this portion of the article, we will discuss and compare these 2 functions. The COUNTA function returns the number of populated cells in a specified range. We can’t apply any conditions in the COUNTA function. But on the other hand, with the COUNTIF function, we can use specific conditions to count cells according to our needs. Let’s say, we have the Price List of ABC Store as our dataset. Now, use the following steps to demonstrate the COUNTA and COUNTIF functions.
Steps:
- Firstly, use the following formula in cell C16.
=COUNTA(C5:C14)Here, the range of cells C5:C14 refers to the cells of the Price column.
- Following that, press ENTER.
Subsequently, you will get the count of total populated cells of the Price column in cell C16.
- After that, apply the formula below in cell C17.
=COUNTIF(C5:C14,">1500")- Then, hit ENTER.
Consequently, you will get the count of Product that has a Price greater than $1,500, in cell C17 as shown in the following picture.
Read More: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by 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 and COUNTA functions together in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.
Related Articles
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
