Are you trying to count cells with text in excel? In this video I'll show you how
to count cells that include values and text how to count cells with only text
and how to count the number of times specific text occurs in a range.
Hey I'm Sharyn from Excel at Work where we offer straightforward non-technical assistance
to help you save time, work smarter and improve your Microsoft Office skills.
Now if you're using my 100 plus Steps to Proficient workbook to improve your Excel skills,
I've popped the topic number this video relates to in the description box. If you haven't yet
claimed a copy of this workbook, I've also added a link to where you can grab yourself a copy.
Right, let's look at how to count cells including text in Excel. So here we have the COUNT function
and as you can see it's looking down a column full of text. Now unfortunately we're returning a zero
and that's exactly what the COUNT function does. So it's only counting values. If I came in here
and changed paid to one two three, being a number or a value. Look. It will actually count a number
and there's only one number in that column. So you might be finding this as a problem because it's
only picking up values and not text. So how do we actually count values and text it's very simple,
we can use something called the COUNTA function. So I'm just coming up here,
I'm going to pop an a at the end of the word count and that changes the function to COUNTA.
Now when we do this, we now have a function that will allow us to count everything. So I'm just
going to go over here and show you this function counts the number of cells in a range that are not
empty. Which means it will count both text and numbers and give you the total and 20 is correct
but what if we're only wanting to count the number of cells that have text and eliminate the
cells that have numbers? So we can do this we can actually come up here and add to our formula minus
and use the COUNT function going over the same range and closing the brackets and of course what
will happen is the COUNTA will count all cells and then the COUNT will count how many cells hold a
value or a number and of course that'll subtract from the total number of sales that the county
returns giving you the correct number. So there's 19 cells that have text in them. So if I come down
and change this back to paid. Look at that. It changes to 20. Now there is another way we could
do this we could use the COUNTIF function. I'm just going to come up here. Now equals, COUNTIF,
open bracket, going over the range and then we're using a comma and our criteria is double quote,
asterisk, double quote and closing the bracket and that will give you the same result. But what
if we want to count the number of times specific text occurs? For example, if we wanted to know how
many records had the status of outstanding. So, to do this we can use the COUNTIF function again.
So I'll just press my DELTE key and what we're going to do is we're going to go, equals, COUNTIF,
open bracket, we'll go over the range again,
comma and we'll type inside double quotes the word outstanding.
Now, it's not case sensitive. So you don't need to have an uppercase, lowercase,
proper case here. Excel will find the word outstanding regardless of what case it's typed
in. Closing the bracket and pressing ENTER and there we go it's actually saying that we've got
nine records of outstanding one, two, three, four, five, six, seven, eight, nine perfect.
So the COUNTIF, I'll just go into here again, is looking down a range and it's looking for
specific text. Please make sure that you spell it correctly or it will not find it and for one last
option if you are looking for specific text you can use something called the COUNTIFS function
that will allow you to do a count using more than one criteria. Let's have a look at this.
I'm just going to press my DELETE key. If I go equals COUNTIFS this time, so we're looking at
the plural rather than the singular COUNTIF. So what we're going to do now, you're looking at
the criteria range one, so we're going to go down here and we're going to set that as outstanding.
So we're actually saying to Excel please go down that column and look for anything that has a
status of outstanding. Another comma and we're now moving on to criteria range two. Now watch
this. I can go over to another column highlight that comma, this time I'm actually going to say A
N Example limited. You'll see that it's not in the same case as what's in the actual spreadsheet
itself and I'm just using my double quotes to close that and I'm closing that second criteria.
Now you can build multiple criteria you don't have to just have two. I'm going to press my Enter
key and it's saying that there's four. So what's happening here is Excel is looking for outstanding
and it must be the supplier name of A N Example limited. So we've got 1 there we've got 2, 3,
4 down here. So that's perfect. So COUNTIFS allows you to be even more strategic in counting
the number of times specific text occurs. Not only in one column but also where we can
test for multiple criteria. Now if this has been helpful please let me know in the comments below
and if you use Excel and want to stay connected please consider liking, following, subscribing
because I'm sharing new videos regularly and if you know someone who would benefit from this help,
please be sure to share this video with them. Hey thanks for watching and have a fabulous day.