Video Transcript (Click to Expand)
Hi, this is Gary with MacMost Now.
On todays episode let's look at how to count items in iWork Numbers.
So in this simple example I've got a bunch of different items over here and I want to count how many are there of certain types, so how many apples are listed over here on the left
So I've create another table over here, I've got a place to put my formula and I'm going to start by typing the equals sign.
Now, any time you want to do something in Numbers of course, you need a formula, and any time you want to do something specific like counting, you need to get to the right function.
So click the "Function" button here, and it comes up with a list of all the different functions. So what you want to do is browse through it and see what fits your need.
So, for instance, for counting you can see I've got a bunch of different things here if I select one, it'll show me the definition here, and I can see, for instance, that this will return the number of thing that contain numbers or numerical expressions or dates. That's not what I want.
"COUNTA" will actually return things that are not empty, so if I did that it would just count everything in here.
"COUNTBLANK" is kind of the opposite.
"COUNTIF" returns the number of cells that satisfy a condition so that is the one that I want.
So let me use "COUNTIF" and then the first thing I want to do is give it an area, so I'm going to give it this column here in this table.
And the second thing I want to do is give it a condition, and the way you do that is you use quotes. So quote equals Apple end quote, and then close parenthesis and then return and I can see it counts the number of apples there.
So now I want to do the same thing, say, for the other ones.
So for oranges I would then enter in the formula "COUNTIF" click on the column heading there and "equals Orange" and I can continue for bananas and peaches.
Now, let's make things a little more complex, say I just don't want to look at one column but I want to look at several.
Like here I've got a store column, store A and B, and I also have a checkbox for wether something is on sale or not. I can do that with the "COUNTIFS" so now I can continue to add pieces to the formula.
So, for instance, I could say this first column here and "it's got to be apples" then continue and say in the second column "it's got to be store A" and then I can continue with the third one and say "OK, in this one it's got to be true."
The result is I now can see that there are three apples that are from, being sold in store A and are on sale.
Let's make things a little bit more complex, say I want to not only get the count, but I want to get the total of the price of those items, I have in this price column now.
Well, for that instead of using "COUNTIF" I can do "SUMIF."
So, for instance, "SUMIF" and then I would say column "A" and the condition is "It's got to be an apple" and then I would use "D" as the column I want to get the amounts for the sum. And you can see it's $3.80 worth of apples.
Now, let's say I want it to represent this number here, this number here is the number of apples in store A that are on sale.
So I actually want this to match that. I'm going to copy this formula from here, and I'm going to paste it into here, and change this "COUNTIFS" to "SUMIFS" so I can use the same conditions but then I'm going to insert at the beginning of this the price column.
So you notice that the "SUMIF" is column, condition, and then the sum column.
This, the column S, flips it around and puts the sum column first and then a list of conditions.
And then when I do return I see I get $1.80 worth of apples that are in store A and on sale and the count is three.
So there you go, there's's how you use the "COUNTIF," the "COUNTIFS," the "SUMIF" and the "SUMIFS" formulas in Numbers.
Hope you found this useful.
'Til next time, this is Gary with MacMost Now.