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 theargument.*range* **C5:C8**â†’ This refers to theargument.*criteria***Output**â†’**{1;1;1;1}**.

- Here,
**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.**

- Here,
**COUNTA(B5:B14)-SUM(COUNTIF(B5:B14,C5:C8))**â†’ It becomes**COUNTA(B5:B14)-4**.- Here,
**B5:B14**â†’ It is theargument.*value1* **Output**â†’**6**.

- Here,

- 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 theargument.*range* **“=0”**â†’ This refers to theargument.*criteria***Output**â†’**2**.

- Here,
**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**