COUNTIF()

COUNTIF() is a function in Excel that counts the number of cells that meets a certain condition.

=COUNTIF(range, criteria)

range: The range of cells that you want to evaluate.

criteria: The condition or criteria that you want to apply to the range.

For example, if you had a list of customers in Malaysia, and you want to know how many customers you have in each state, you could use COUNTIF().

First create a list of your customers with their states in two separate columns.

Your Excel sheet would look like this:

Next, in a separate column, create a list of the states in Malaysia.

Your worksheet would now look like this:

Now in cell F2, type in =COUNTIF(

Next select all the rows in column B starting from cell B2 to B20

When you have highlighted the rows, press the F4 key to make it an absolute reference.

When you press the F4 key, the reference to the range in cell F2 will change from =COUNTIF(B2:B20 to =COUNTIF($B$2:$B$20.

An absolute reference ensures that the range B2:B20 does not change when you copy the formula to the cells below.

Next, type in a comma, then select the value in cell E2 in the Malaysian States column.

Here you do not need to make the reference absolute as you want the cell address to change when you copy the formula down to the other cells.

Close the bracket and press enter to complete the formula and display the result.

Your formula should now look like this:

=COUNTIF($B$2:$B$20,E2)

$B$2:$B20 is the range you want, while E2 is the condition or criteria.

In simple terms, you are asking Excel to check values in column B from B2 to B20 and if it is the same as the value in E2, then count it.

You can now see how many customers you have in the state of Johor.

To copy the formula down to the other cells, with your mouse, point to the bottom right of cell F2 until the cursor looks like a + then click, hold and drag down to cell F20.

When you release your mouse, the formula will be copied down to the relevant cells, and you will see the count of customers in each state.