# How to Use COUNTIF and COUNTA Functions Together in Excel

Suppose you have the following data set:

### Example 1 – Counting Cells While Excluding Some Specified Cells

Steps:

• Choose an appropriate cell, and enter the following formula:
`=COUNTA(B5:B14)-SUM(COUNTIF(B5:B14,C5:C8))`

Formula Breakdown

• COUNTIF(B5:B14,C5:C8)
• In this example, B5:B14 is the range argument.
• C5:C8 refers to the criteria argument.
• SUM(COUNTIF(B5:B14,C5:C8)) becomes SUM({1;1;1;1}).
• COUNTA(B5:B14)-SUM(COUNTIF(B5:B14,C5:C8)) becomes COUNTA(B5:B14)-4.
• B5:B14 is the value1 argument.
• Press ENTER.

The result should be a count of cells excluding the cells of the Excluded Product.

### Example 2 – Counting Cells Without a Null Value and 0 Value

Steps:

• Use the following formula in an appropriate cell (B7) to get a null value as output:
`=IF(2>1,"",0)`
• In a relevant cell (C16), enter the following formula:
`=COUNTA(B5:B14)-COUNTBLANK(B5:B14)-COUNTIF(B5:B14,"=0")`

Formula Breakdown

• COUNTIF(B5:B14,”=0″)
• B5:B14 is the range argument.
• “=0” refers to the criteria argument.
• COUNTBLANK(B5:B14)The COUNTBLANK function will return the number of blank cells in the range  B5:B14.
• COUNTA(B5:B14) returns the count of populated cells of the Product column.
• COUNTA(B5:B14)-COUNTBLANK(B5:B14)-COUNTIF(B5:B14,”=0″) becomes 10-1-2.
• Hit ENTER.

The result should be a count of cells that do not have a null or 0 value.

### COUNTA vs COUNTIF Functions in Excel

Suppose you have the following data set:

Steps:

• In the correct cell (C16), enter the following formula.
`=COUNTA(C5:C14)`
• Press ENTER.

The result is a count of the total populated cells of the noted column.

• In the next cell (C17), apply the formula below:
`=COUNTIF(C5:C14,">1500")`
• Hit ENTER.

This result is a count of only those cells that meet the noted criteria (cells that have a Price greater than \$1,500).

### Related Articles

<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF