COUNTIF
Excel COUNTIF Function
Excel has a built-in function called COUNTIF that counts cells according to a formula.
The type is =COUNTIF.
NOTE: There are both simple and complex applications for the COUNTIF function. This covers the fundamental application of counting certain words and numbers.
It is possible to specify words (like “Water”) and numbers (like 90).
How to use the =COUNTIF function:
- Select a cell
- Type =COUNTIF
- Double click the COUNTIF command
- Select a range
- Type ,
- Select a cell (the criteria, the value that you want to count)
- Hit enter
Note: A symbol, such as a comma or semicolon, is used to separate the various components of the function;
The symbol is determined by your language preferences.
Here are a few illustrations!
Utilizing range B2:B21, apply the COUNTIF function to get the quantity of each type of Pokemon we have:
In the range G5:G15, we want the COUNTIF function to count the different kinds of Pokemon.
Once the range has been chosen , you type the to inform the function what you want to count.
Victreebel, a Grass Pokemon, has been correctly tallied by the COUNTIF function (A4).
Let’s tally the G6 Water Pokemon.
The same procedures are followed.
- Select G6
- Type =COUNTIF
- Select B2:B21
- Type (,)
- Select F6 (Specifying Water as criteria)
- Hit enter
That is fantastic! 6 Water Pokemon have been counted in B2:B21 by the COUNTIF function.
To make things more efficient, let’s count the remaining types. We wish to carry on with G6:G15 is operation. Using Absolute References and the Filling Function.
Step by step:
- Double click G6
- Lock the range references absolute (B2:B21). Type dollar signs before the columns and row. Type 4 dollar signs in total. =COUNTIF($B$2:$B$21,F6). Note: We want F6 to remain relative. Because we want it to move downwards. Do not add dollar signs ($) to it.
- Hit enter
- Fill the range G6:G15
That really helped us save time! Well done!
A Non-Working Example
Let’s attempt an ineffective example.
See what happens when you fill G5:G15 without locking the references.
The fill function will shift the range downward, encompassing blank cells and omitting the range containing the data, if the range’s references are maintained relative.
