Count Items In Numbers

You can count the number of times a text string appears in a Numbers spreadsheet column using the COUNTIF function. The function can be hard-coded to look for text, or it can use the value of another cell.

Video Transcript: So let's say you've got a spreadsheet in Numbers that has a whole bunch of different items and you want to count them. You want to find out how much of each one there are. 
Here I just have a simple example of that. Actually in this table here this goes pretty long. It's 500 rows. So it's not going to be easy for me to figure out how many of each of these words are there. 
But I can do it using a formula pretty easily. So let's create a new table here. I'll create a standard table. Let's shrink it down a bit. I only really need two columns here. 
In this first column let's put the item we want. So let's count the number of apples that appear here. Then here is where I'm going to put the formula. So I start by typing = (equals) and I need a function. So whenever you need a function just search for something. 
We want to count the number of items so let's start by searching for count. Sure enough there are several different count functions. Let's look at them. The first one here COUNT the number of its arguments that contain numbers, numeric expressions, or dates. That's not what we want. Not Empty so COUNTA is not what we want. COUNTIF will return the number of cells in the collection that satisfy given conditions. So this sounds like what we want. We want to see how many cells here equal something.
So let's select COUNTIF. I could've typed it by I'm just going to double click it there to insert it. I can see the parameters here.
So the test array. So that's going to be, well we just want the whole column here. So I could select everything but instead I'm going to do just click on B there and it will show Table 1, Column B. Great. So what's the condition. 
For the condition let's we can see here in the examples how to do that. We can see that we've got things with quotes around them like Quote=ipsumUnqote. So let's do that here. Quote=ApplesUnquote like that. So that seems to fit the example here and let's hit Return and we get 66 which seems reasonable. That's what we would expect here. We've got 500 items so 66 is probably correct.
We can do the same thing. I can copy this and paste it here and then we edit the formula. I can change it to say Quote=BananasUnquote. It shows there is 110 bananas there.
But what would be nice is if we didn't have to have apples here and apples also here in the formula you can see there below. So let's alter this a bit. Let's say what if instead of =apples we simply put a link to this cell here. So Table 1::B and the condition is that they should be the same as A2 which is apples. We get the same 66.
If I were to, let's delete that cell there, and copy this cell and paste it in here we get 110 because while the column remains the same, we've only moved down vertically so column B remains the same there, this moves down from A2 to A3. So as we copy and paste it there we get zero but that's because we don't have anything here. So let's count oranges and we get 88. We can just put pineapples there and then we copy and paste and we get 102.
All it's doing is taking the value of this, comparing it to this entire range here, and then giving us the total for each. So a pretty simple way to count the number of items in a Numbers spreadsheet.

Comments: 2 Responses to “Count Items In Numbers”

Jeannie

7 years ago

This post, with instructions on how to use CountIf is really really helpful for novice researchers needing to see the frequency of words when analyzing text. And therefore not having to buy expensive software!

Thank you, Gary.

Shirley

That was so very useful as I have to count Computer Club members who are PC, who are Mac and who are Both. You just made my job a lot easier!
Thank you!

Comments Closed.

Count Items In Numbers

Comments: 2 Responses to “Count Items In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information