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

**Table of Contents**hide

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

**Similar Readings**

**How to Use COUNTIF Function In Excel to Count Bold Cells****How to Use COUNTIF Function to Count Text from List in Excel****Excel COUNTIF to Count Cell That Contains Text from Another Cell****Count Text at Start with COUNTIF & LEFT Functions in Excel****How to Compare Two Columns Using COUNTIF Function**

## 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 COUNTIF Function to Calculate Percentage 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

**How to Use Excel COUNTIF Between Time Range****How to Use COUNTIF to Count Date Less Than Today in Excel****Excel COUNTIF to Count Cells Greater Than 1****COUNTIF Function to Count Cells That Are Not Equal to Zero****How to Use COUNTIF Function in Excel Greater Than Percentage****How to Use COUNTIF Function with Array Criteria in Excel****How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel****How to Use the Combination of COUNTIF and SUMIF in Excel****Difference Between SUMIF and COUNTIF Functions in Excel****How to Use IF and COUNTIF Functions Together in Excel****How to Use Nested COUNTIF Function in Excel****How to Calculate Frequency Using COUNTIF Function in Excel****Excel COUNTIF Function with Conditional Formatting****[Solved!]: Excel COUNTIF Returning 0 Instead of Actual Value****[Fixed] COUNTIF Function with Wildcard Not Working in Excel****[Fixed!] Excel COUNTIF Function Not Working for String “True”**