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.

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, the
**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:** **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 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:** **Count Blank Cells with Excel COUNTIF Function: 2 Examples**

**Similar Readings**

**How to Use COUNTIF Between Two Numbers (4 Methods)****Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)****How to Use IF and COUNTIF Functions Together in Excel****Excel COUNTIF Function with Conditional Formatting (7 Examples)****How to Compare Two Columns Using COUNTIF Function (4 Ways)**

## 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:** **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.

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

**COUNTIF Excel Example (22 Examples)****How to Use COUNTIF Function in Excel Greater Than Percentage****COUNTIF vs COUNTIFS in Excel (4 Examples)****How to Apply COUNTIF Between Two Cell Values in Excel****VBA COUNTIF Function in Excel (6 Examples)****How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**