How to count how many times a value appears in a column

You can watch a video tutorial here.

So you have a list of multiple values and would like to count how many times a certain value appears within the list.

There are four main ways to use the COUNT function, but I have broken them down into two:

COUNT – Counts the number of cells with a number value

COUNTA – Counts the number of non-blank cells

COUNTIF – Counts the number of times a specific value appears

COUNTIFS – Counts the number of times a specific value appears using more than one criteria

Option 1 – COUNT / COUNTA Functions:

Step 1 – Understanding the Syntax

• Both COUNT and COUNTA only require the range you would like to count.
• In the following example, I want to count the values in Column A.

Step 2 – Counting Text Values

• In the example I am using, the values in Column A is text and a normal COUNT function will not work, as it only counts numerical values.
• The COUNTA function should be used in this case, as it counts all non-blank cells – REMEMBER to take this into account if you have headings!

Step 3: Counting Numerical Values

• In the example I am using, the values in Column B are numerical and a normal COUNT function can be used.
• The COUNTA function may also be used in this case, as it counts all non-blank cells.

Option 2 – COUNTIF / COUNTIFS Functions:

Step 1 – Understanding the Syntax for COUNTIF

• First, you need to specify the range that contains the values to be counted.
• Secondly, you need to specify the value that needs to be counted.
• In this example, the formula refers to the value within cell H1, but you can also type out a value you require manually, by typing the value between quotation marks e.g. “Apple”.

Step 2 – Understanding the Syntax for COUNTIFS

• First, you need to specify the range that contains the values to be counted.
• Secondly, you need to specify the value that needs to be counted and repeat the process for any other criteria.
• In this example, I want to count the amount of times the Apple value appears with a 12 in column B. The formula refers to the values within cell H1 and G3, but you can also type out a value you require manually, by typing the values between quotation marks e.g. “Apple” and “12”.

Final Results:

This is an easy way to count values in Excel and no VBA is needed.