Excel CountIf
The COUNTIF and COUNTIFS functions are used to aggregate data as counts.
Contents
COUNTIF
Consider the below spreadsheet:
|
A |
B |
C |
1 |
Name |
Score |
Days Sick |
2 |
Alice |
91 |
0 |
3 |
Bob |
54 |
7 |
4 |
Charlie |
87 |
2 |
5 |
Daisy |
84 |
0 |
6 |
Edward |
76 |
1 |
To count the number of students with scores below 85, use the COUNTIF function like:
=COUNTIF($B:$B, "<85")
The second parameter is called the criteria. A criteria can be any of:
An integer literal (32)
A string expressing a logical comparison (">32")
A cell (B4)
To use a logical comparison with a cell's value, try string concatenation (">" & B4)
A string literal ("apples")
Integers can also be expressed as string literals due to duck typing ("32")
Question marks and asterisks are treated as wildcard characters
COUNTIFS
To count the number of students with scores below 85 and no sick days, use the COUNTIFS function like:
=COUNTIFS($B:$B, "<85", $C:$C, 0)
Every range must have the same number of rows and columns. Otherwise a #VALUE! error propogates.