There are several instances when you need to count the number of cells that contain a certain value.
For example, you want to:
- Count if a product code is duplicated or not
- Count the existence of a text string in a given range of cells
To do this, there are several different ways in Microsoft Excel.
Method 1: Use Sum & If Functions in Excel
=SUM( IF(range=”text”, 1, 0))
Note that this is an array formula, meaning that it must be keyed in a special way. Do not simply press Enter key to finish typing this formula. You must press CTRL + SHIFT + ENTER together.
Once you key in the array formula, it looks like below ( this formula searches for the value of 5 inn the cell ranging from A1 to C3. If it finds a 5, it counts a 1. All the counts are then Summed Up.)
=SUM( IF( A1:C3 = 5, 1, 0) )
Want to Improve Your Excel Skills?
Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.
To Get Most out of Excel, Learn the Pivot Table techniques in our
Pivot Table Masterclass Training!
Method 2: Use the inbuilt Countif Function of Excel
=COUNTIF(A1:C3, “text”)
Note that this is a simple and straight forward way. It is not considered an Array function. Just key it in, and press the Enter key.
Voila, it shows you the correct count.
There you are! Two Simple ways to count the number of occurrences of a value in a range of cells in Excel.
These above 2 methods work in all versions of Excel, on the PC and on the Mac.
Hope you will benefit from them!
Cheers,
Vinai Prakash
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!
Vinai conducts workshops to enable all Excel enthusiasts in learning simple tips, tricks, techniques in Excel. Join a 2 day workshop in Singapore, or email us the city where you want to have the workshop conducted. We can
