Difference between all 5 count functions (COUNT, COUNTA, COUNTIF, COUNTIFS and COUNTBLANK) in Excel.

What is the difference between COUNT, COUNTA, COUNTIF, COUNTIFS and COUNTBLANK in Ms-Excel?

Suppose we have a table like this.

Excel Data for Count, CountA, CountIf. CountIFS, CountBlank, Function

Different type of count functions

The following table illustrated the differences between each function:

Usage

CountCount number of cells that have numbers or values
CountAcount any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.
CountIFcount cells matching a certain criteria
CountifsSimilar to countif function. However, it allows multiple criterias from multiple ranges nd counts the number of times all criteria are meet
CountBlankCount number of blank cells

Syntax

Count=count(area)
CountA=counta(area)
CountIF=countif(range.criteria)
Countifs=countifs(criteria_range1, criteria1, [criteria_range2,criteria2],…)
criteria_range1, criteria1 are required, while [criteria_range2,criteria2] onward are optional
CountBlank= CountBlank(area)

Example

Count=count(A1:H10)
Result: 22
Explaination: Count number of cells that have numbers or values within the range A1:H10
CountA=COUNTA(A1:H10)
Result:79
Explanation: count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks in the range A1: H10
CountIF=COUNTIF(A1:H10,”A”)
Result: 1
Explanation: Count number of cells only have the character “A” in range A1: H10
Countifs=COUNTIFS(C1:C10,8,E1:E10,”B”)
Result: 1
Explanation:
This time, it have two steps:
1. subset the range C1:C10 to where the cells only have character “B”
2. Then it look for the corresponding values in column E to see how many of them have the secoond value in the corresponding row. In this case, only one row
CountBlank = CountBlank(A1:H10)
Result : 1
Explanation:
Count number of blank cells within the range A1:H10

Leave a Comment