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}).
- The SUM function will return the sum of the {1;1;1;1} array.
- 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.
Read More: Difference Between SUMIF and COUNTIF Functions in Excel
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.
Read More: How to Use the Combination of COUNTIF and SUMIF in Excel
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).
Read More: How to Use SUMIF, COUNTIF and AVERAGEIF Functions in Excel
Download Practice Workbook
Related Articles
<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
