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

How to Use COUNTIF and COUNTA Functions Together in Excel

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.


Download Practice Workbook


2 Appropriate Examples to Use COUNTIF and COUNTA Functions Together in Excel

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.

countif and counta 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.

Counting Cells While Excluding Some Specified Cells by using the COUNTIF and COUNTA functions together in Excel

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: How to Use COUNTIF for Non Contiguous Range 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.

Counting Cells Without Null Value and 0 Value by using the COUNTIF and COUNTA functions together in Excel

Consequently, you will get the following output, as demonstrated in the following image.

Final output of method 2 to use the COUNTIF and COUNTA functions together in Excel

Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples


Similar Readings


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.

COUNTA Vs COUNTIF Function in Excel

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: Excel COUNTIF with Greater Than and Less Than Criteria


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to use the COUNTIF and COUNTA functions together in Excel


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. 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