It seems like a really simple option, but there’s a lot more than you think to how to use the count function in Excel. OK, what I’m really talking about is Count and all of the versions available in MS Excel.
It’s obvious
Sure, the idea of a function called ‘Count’, is completely obvious what it does.
Duh – it counts things.
Ahh – but here’s the cool bit – you can count a number of different things.
Let me explain
Note: If you’re new to functions, check out my other post Formulas and Functions – What you need to know to get you started.
The Count Function
Let’s start with the basic function
=Count(Range)
Yes, you’re absolutely right – it counts the number of items in the range that you’ve selected.
However, here’s the ‘thing’ it will only count the cells that contain numbers.
As you can see although the range is the same – rows 1 to 7 – there are 7 numbers in column A, but only 4 in column D.
Now you may wonder why you’d use this option. I mean, you can count that easily yourself (and it doesn’t have to mean manually counting the cells).
Well if you’ve got a mix of names and numbers then counting the numbers by themselves could be useful – OK, I’ll admit this isn’t something that I use all the time.
However, if you expect a certain number of items – say codes or purchases, then you could use count as a checking option.
Set a Count up, and use it to check that you’ve got all the information entered, that you expected.
CountA
Unlike Count, the CountA version does count all of the information.
If you haven’t figured it out – A stands for All.
=CountA(range)
In the previous example, with a CountA function they would have the same answers (7), simply because it’s the same number of cells in the two ranges.
CountBlank
Out of the variations of Count, this is one of the ones that I think can be very useful.
Based on the function name, I’m sure that you’ve worked out, it will count the number of blank/empty cells.
=CountBlank(Range)
I love to use this as a check when you’ve got to have information in all the cells. In this use example, we want the answer to be 0. Anything else means that you’ve missed something.
It’s not a perfect option, because it will let you know that something is missing, but not where it’s missing. Sorry that’s still a bit of a manual process…
I’m sure that there are other ways that you can use it, but that’s the one that always comes to my mind.
(If you’ve got some alternatives ideas, let me know in the comments.)
CountIF
This is one of the most useful of the Count variations, in my personal opinion. 😊
With almost all of the others, they count specific things, and while CountIF, still counts specific things, you get to choose what it counts.
=CountIF(Range, Criteria)
In simple terms, CountIF will count the number of times something (that you specify) appears in the range of cells that you select.
Range = the cells to look at
Criteria = what you’re looking for.
Couple of tips for criteria – have what you’re looking for in a cell (by itself), then in the function, reference that cell address. I’ve found that it seems to work better that way.
Note: It will work if you put the information into the function, remember you can type a number in but if it’s text it should be in double quotes (“). It’s one of the reasons that I suggest typing the criteria into it’s own cell.
Of course the other reason for the criteria to be in it’s own cell is that you can copy for the formula to other cells without having to re-write it each time.
Note: I’m sure you can recognise most of the dates, if you’re curious, 18th Apr was Easter Monday 2022 😉.
You can see from the example above, that I copied the formula down for the other three dates, rather than having to type in the specific date.
Note: If you’re not sure why there are $ in the range address, have a read of What’s Relative and Absolute Addressing in a Spreadsheet?
Out of all of the Count variations, this is the one that I find myself using more often, although you might prefer then next one…
CountIFS
The ‘catch’ with CountIF, is that it only counts one option, with the newer option CountIFS you can expand it.
=CountIFS(Criteria_Range1, Criteria1, Criteria_Range2, Criteria2)
Note: While I’ve only entered a couple of options, Excel will allow you enter up to 127 criteria’s in a single formula.
Things to be aware of:
When Excel reads this function, all of the criteria options have to be true for it to be counted. Adding more and more criteria in, while making it very specific, will mean that your answer is more likely to be zero. So please be careful about adding too many criteria’s in.
In this example, I’m looking for each region, where the orders are greater than or equal to 50. Seems like the people from the North region are doing best.
Breaking down the function:
The first range is looking at the region (col B), and then for each specific location, starting with East.
The second range is looking at the number of orders (col C), and then for more than (>) or equal to (=) 50.
Only where both of these are true, will the people be counted.
(I went blank thinking of an example, so I borrowed one from Microsoft https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)
Any others?
Well, yes, there are actually CubeSetCount, DCount and DCountA.
Based on there names alone – completely obvious right 🤣
Microsoft Excel had ‘Cube’ functions added with Excel 365 – and I’ll admit as I don’t use them, I’m a little fuzzy on them.
A function that starts with ‘D’ is for a database (and part of that category of functions). Like Cube, I’ve managed to avoid them thus far, and I’m not interested in breaking my current record of non-use.
I’ve mentioned them only to have a complete list of Excel Count Functions.
Next Steps
Well if you haven’t already got it, my 5 Easy Excel Functions That Will Save You Time free guide is a great place to start. Although I’ll admit that there’s only 4 relevant if you’ve read through this post…
Where will you find a use for the Count Functions? Share your examples in a comment.
Related Posts
- Complete List Of Options Where You Can Customise the Colour
When it comes to colour in your office files, we rarely move beyond changing the font or fill colour. But there is so much more to play with when you customise the colour. Read through the post, or jump to your favourite office suite – Google or Microsoft. Google Let’s start with Google, as it’s… Read more: Complete List Of Options Where You Can Customise the Colour - How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics!
how to use the count function in excel, excel count function, count function in excel