What is the difference between COUNT, COUNTA, COUNTIF, COUNTIFS and COUNTBLANK in Ms-Excel?
Suppose we have a table like this.
Different type of count functions
The following table illustrated the differences between each function:
Usage
| Count | Count number of cells that have numbers or values |
| CountA | count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks. |
| CountIF | count cells matching a certain criteria |
| Countifs | Similar to countif function. However, it allows multiple criterias from multiple ranges nd counts the number of times all criteria are meet |
| CountBlank | Count 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 |