Counting Items With Numbers

Sometimes you want to be able to count the number of items in a table and get the total. You can do this with the COUNTIF function. It will return the numbers of items that match a certain criteria. You can also use the COUNTIFS function to match multiple criteria in a more complex table.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Let me show you how to count items in Numbers.
Let's use Numbers, version 3 and learn how to count items in Numbers.
So I have a sample spreadsheet here and I'm going to populate it. We're going to try to count the number of times certain words appear here in this list. This could be item names, product ID's, whatever. 
Let's try to count the number of times this appears. To do that I'm going to create another table here and shrink this table down. I'm just going to use it as a place holder now to put our function. So we're going to see how often this appears in the table by using a function here to count them.
Now there is no magic to figuring out what formula goes here, what function we need. We hit the equal sign and the Functions browser comes up. We can search through here and look and see what has to do with count. So we see, oh there's a count function. Maybe that's it. As it turns out that is not it because it is basically going to count the number of objects that contain something in it. So let's continue down the list and we can see that this counts the number of things that are not empty, and this counts the number of cells that satisfies a given condition. That is kind of what we want here. So let's try that.
So we will do COUNTIF and look at some of the examples here. We can see how you enter in a formula like that. Let's try something just like this. We can go and say COUNTIF and then give it this whole range here. Actually we will just do the entire column B; so Table 1 Column B and then we're going to, in quotes, put equals this. Just like that. Let's see if it gives us the correct result. Indeed it does! It gives us the fact there is four of these over here.
Now one of the things that we could do is to copy this, paste it here below and it is going to say four. But we're going to make this row about that. We're going to change the formula here and have it count the number of times that appears. It appears three times which is correct. So great. So we've got what we need and we can do other. Take the same formula. Copy, paste, and in here we can change it to other. 
Of course, being formulas, these will automatically change as the data changes. So if we change other to as this, we will see the number of this's go up and the number of others go down automatically. We don't have to do anything. It just changes to reflect the data.
Likewise since we are using the entire column here we can add another row and add a this and you can see this will go up as well. We don't have to tell it to increase the size of the range because we told it the range was the entire column.
Now there is another function you should know about for counting. That allows you to count more than one thing. So we were using COUNTIF but what happens if, instead of COUNTIF, we use COUNTIFS. COUNTIFS is basically the plural of COUNTIF. We can add in several different things.
So let's start off with test values and let's say the test values here are going to be B and we want the condition to be equals this as before. But we also want to look under C and set it to be the condition equals yep. It will only count the items where both of these conditions are met. So we end up with five of those. So it is only counting one, two, three, it is not counting that one, four, five. So you can see it is meeting two conditions. 
You can do three or even more conditions with this. It is pretty easy. You just add in another two things here. You've got the range and condition, range and condition. I can add another range and condition and keep going.

Comments: 2 Responses to “Counting Items With Numbers”

Jose Osorio

11 years ago

Hi Gary. I was wondering why you write "=this" instead of referring to the cell itself, for instance B1. I tried it on mine and it did work. It would make it easier when copy pasting..

Thanks for your great videos, I am constantly learning with them ;-)

Gary Rosenzweig

Sure, you can do it either way. It might not always be the case that you are using a heading column with exactly the same text as what you are matching. But if you are, then using a reference saves time.

Comments Closed.

Counting Items With Numbers

Comments: 2 Responses to “Counting Items With Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information