COUNTIF Function
The COUNTIF function is a premade function in Excel, which counts cells as specified.
It is typed =COUNTIF
How to use the =COUNTIF function:
1. Select a cell
2. Type =COUNTIF
3. Double click the COUNTIF command
4. Select a range
5. Type ,
6. Select a cell (the criteria, the value that you want to count)
7. Hit enter
Note: The different parts of the function are separated by a symbol, like comma , or
semicolon ;
se COUNTIF, one of the statistical functions, to count the number of cells that meet a
criterion; for example, to count the number of times a particular city appears in a customer
list.
In its simplest form, COUNTIF says:
=COUNTIF(Where do you want to look?, What do you want to look for?)
For example:
=COUNTIF(A2:A5,"London")
=COUNTIF(A2:A5,A4)
Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description
=COUNTIF(A2:A5,"apples") Counts the number of cells with apples in
cells A2 through A5. The result is 2.
=COUNTIF(A2:A5,A4) Counts the number of cells with peaches
(the value in A4) in cells A2 through A5.
The result is 1.
=COUNTIF(A2:A5,A2)+COUNTIF(A2:A Counts the number of apples (the value
5,A3) in A2), and oranges (the value in A3) in
cells A2 through A5. The result is 3. This
formula uses COUNTIF twice to specify
multiple criteria, one criteria per
expression. You could also use
the COUNTIFS function.
=COUNTIF(B2:B5,">55") Counts the number of cells with a value
greater than 55 in cells B2 through B5.
The result is 2.
=COUNTIF(B2:B5,"<>"&B4) Counts the number of cells with a value
not equal to 75 in cells B2 through B5.
The ampersand (&) merges the
comparison operator for not equal to
(<>) and the value in B4 to read
=COUNTIF(B2:B5,"<>75"). The result is
3.
=COUNTIF(B2:B5,">=32")- Counts the number of cells with a value
COUNTIF(B2:B5,"<=85") greater than (>) or equal to (=) 32 and
less than (<) or equal to (=) 85 in cells
B2 through B5. The result is 1.
=COUNTIF(A2:A5,"*") Counts the number of cells containing
any text in cells A2 through A5. The
asterisk (*) is used as the wildcard
character to match any character. The
result is 4.
IFS Function
The IFS function is a premade function in Excel, which returns values based on one or
more true or false conditions.
It is typed =IFS and has two or more parts:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3; ...)
The conditions are referred to as logical_test1, logical_test2, ..., which can check things
like:
If a number is greater than another number >
If a number is smaller than another number <
If a number or text is equal to something =
Each condition is connected with a return value.
Example IFS function
Make categories for how fast the Pokemon are:
The conditions and return values are:
Speed more than 90: "Fast"
Speed more than 50: "Normal"
Speed less than or equal to 50: "Slow"
Example IFS function, step by step:
1. Select the cell D2
2. Type =IFS
3. Double click the IFS command
4. Specify the first condition C2>90
5. Type ,
6. Specify the value "Fast" for when the first condition is TRUE
7. Type ,
8. Specify the second condition C2>50
9. Type ,
10. Specify the value "Normal" for when the second condition is TRUE
11. Type ,
12. Specify the third condition C2<=50
13. Type ,
14. Specify the value "Slow" for when the third condition is TRUE
