Using COUNTIF for better data analysis and validation
COUNTIF is a powerful tool that can help you to make sense of large sets of data, pulling out the information you need quickly and easily. Find out how to use COUNTIF in this blog by Happy’s IT HelpLine Manager, Ed Lepre.
Hi, we are Happy
We are leading a movement to create happy, empowered and productive workplaces.
How can we help you and your people to find joy in at least 80% of your work?
COUNT and COUNTA functions count cells that contain either one value or another. To analyse your data using multiple criteria, you need either to use a helper column then tally up the count, or a more complex single cell formula.
This is fine if the database you are using is small, but when we wish to analyse our data using multiple criteria with large datasets, COUNT or COUNTA are simply not up to the job. The COUNTIF function, however, will get it just like that!
In this blog, we’ll look at COUNTIF in a bit more detail and show you how to use it.
What is the COUNTIF function?
When we are trying to count the number of times we have an item appearing in a list of text, a value in a list, or a list of dates, then the function COUNTIF should be used.
In the table below, we have a list of items sold in different centres and areas. We want to know how many times we have sold Toffee.
As you can see, the word “Toffee” appears twice, and it is this value (2) that we want to display in cell D13.
To do this, we would use the following formula: =COUNTIF(D3: D11,”Toffee”)
Structure of COUNTIF
A COUNTIF function has two arguments.
For example: =COUNTIF(D2:D11,“Toffee”)
Lookup by specific criteria using Data Validation
There are times when we want to look up a value for different items quickly, rather than having to keep re-entering the criteria. To do this we would use data validation or a dropdown menu.
The formula would then update each time you select an entry from the predefined list, as you can see below:
In this example, we want to know how many times ‘Chocolate Buttons’ appears in the Item column D. We then want that number to be displayed in cell D15. To do this we need the formula: =COUNTIF(D2:D11,C15)
In this case, the COUNTIF function is part of an expression that returns TRUE when a value exists in a specified range or list, and FALSE if not.
The COUNTIF function simply counts occurrences of the value in the list. Any count greater than zero will pass validation.
Multiple criteria using COUNTIF Function
Alternatively, you can list how many items were sold of each particular item to make it easy to see which item has sold the best – rather than show only one item each time.
In the example below, the list of Items is shown in a new table below the main table with the duplicates removed. The formula then counts the number of times the item appears in range D2 to D11, and displays the result adjacent to the relevant item in the new table.
Now, instead of typing the name of the item into the formula each time, we can use the cell reference for where the name of the item appears. For instance, we would use the cell C15 for Cheesy Crackers.
To do this, we will need to create a list of unique items. We created this second table by copying the list of items in Column D and using the ‘Remove Duplicates’ tool. This ensured that all items in the original table were included, with none missed out or forgotten – which may have happened if we had manually typed this list.
It is a good idea to copy the original range of items or table to other cells, worksheets or workbooks first before removing duplicate data, to preserve the integrity of the original table.
Once we have created our new list, we can populate the Number of Sales column. We can type this formula into cell D15: =COUNTIF($D$2:$D$11,C15)
We can then copy it down to the end of the new Item list, cell D20. By fixing the range (using Absolute Cell References), the formula will search in a consistent range, and not miss out rows as you copy the formula down.
We now have the number of sales for each item, displayed against its name.
Greater than and smaller than
If you want to count the number of elements larger (or smaller) than a certain value, add your second parameter as a logical test instead (such as > 100, > = 100, <50, etc.).
In the example above, you can see that the number of items that sold 100 units or greater is seven.
To calculate this, you would type the following formula into cell D15: =COUNTIF(F2: F11, “>=100”)
Note that as the logical test will contain characters other than numbers, i.e. > and =, you will need to type your parameter within speech marks (“”).
Why not sign up to our newsletter?
Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.
Ed is a Microsoft Certified Trainer (MCT) and has worked at Happy for over 12 years. Although he trains the full suite of MS Office and non-Office packages at all levels, his favourite package is Excel. A previous winner of the prestigious Learning and Performance Institute's 'Learning Professional of the Year' award, as well as other training accolades, he is also the IT Helpline Manager.
Testimonials from happy Customers
Rick Smallman1 day ago
good delivery & pace - communication after the course could i prove with follow up check in's being offered - perhaps 1 per candidate to review usage/questions
Leigh Urban2 days ago
Knowledgeable and engaging facilitators, delivered the course very well online.
John Lloyd2 days ago
The course was overal well presented via Teams which can be difficult. Some minor logging in issues. The course pdf supplied separate beforehand would help, i.e. ...
Fiona O'Neill2 days ago
Practical tips on using liberating structures. Chance to meet people from other sectors as well as my own. Relaxed and friendly.
Steve Woodruff2 days ago
Well presented, good pace course
Amanda Middleton4 days ago
yes, the course was well structured and the trainer was competent. It was a little cluncky being on line but that was niotv Happy's faault
Marjory Mackay6 days ago
Engaging and stimulating day - with useful methods for virtual networking.
Andrea Randall-Smith11 days ago
I enjoyed their conference and how interactive it was. Excellent communication with attendees and interesting topic.
David Engle11 days ago
Had some problems with getting the right link to the conference, but it was handled relatively well.
Trusted Customer12 days ago
Replied promptly to emails, were very helpful.
Susanna Farley12 days ago
The course was a good one, and the tutor was knowledgeable. However, for me it would have been so much better had it been face-to-face rather than online. I had a numbe...
Caroline Grant13 days ago
Good overview of 365 but would have like more interaction. Being shown things and them trying them out. Not clearly structured