MacMost Now 658: Counting Items In iWork Numbers

You can use the COUNTIF function to count the number of times an item appears in a column. You can also use COUNTIFS to total the number of rows based on several conditions in several columns. The SUMIF and SUMIFS functions will give you the total of the rows in another column based on the same criteria.

Video Transcript
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.

Comments: 14 Responses to “MacMost Now 658: Counting Items In iWork Numbers”

    Alice Cooper
    1/18/12 @ 1:59 am

    Fantastic! Just what I was after – clear, concise instruction. Many thanks!

    Michael
    6/28/12 @ 1:50 pm

    I have ran a mock version of your COUNTIF and COUNTIFS using the same data that you have but I keep getting an error. CountIFS requires that all range arguments and the same size. The column are the same length (size).

    what is going on PLEASE?

    =COUNTIFS(Table 3 :: A,”=A”,Table 3 :: B,”=1″,Table 3 :: C=TRUE)

      6/29/12 @ 12:22 am

      Hard to say without being there. Try again. Try creating your own, etc.

    Al
    12/3/12 @ 8:15 pm

    I would like to use iwork numbers template “grade book”, but would like to use check-box but keep the weighted %. For example a check represents all (100%) of the weighted 15% and this full credit of the 15% would be considered in the final grade column. Conversely with the non check getting none of the 15% in the final grade column. How does on do this? I hope this make sense?

      12/3/12 @ 8:25 pm

      Not sure what you mean. Would it work to simply use an IF function and award 15 points for a check, 0 otherwise? Then add up all the checkmarks?

        Al
        12/3/12 @ 8:49 pm

        I guess I would have the check still be a percentage (0 or full 15%) with other % grades. Then the would go into the total (100%) and letter grade column. Basically grade/no grade but still weighted and tallied to the total and letter grade. Forgive me for not being clear. As you can tell I’m a novice teacher.

    Matt
    12/6/12 @ 12:47 am

    This was very helpful thank you! Also is there a way to count words in cells that may have sentences? It only counts them if they are by themselves and some of my cells have a lot of text. Thank you so much

      12/6/12 @ 7:08 am

      Not sure what you mean by “it only counts them if they are by themselves.”

    Matt
    12/6/12 @ 11:55 am

    Oh sorry meaning if there is only a single word in the cell.

      12/6/12 @ 12:07 pm

      Oh, I think I see. It sounds like you are asking how to count the number of words in a sentence inside a cell. But I think what you are really asking is how to add up the total number of cells that contain a word.
      For that you’d want to use the SEARCH function to figure out if the word is in the cell. So each cell gets a single cell next to it that uses the SEARCH function to figure that out. Then use one of the COUNT-like functions to count those cells that have a positive answer. It will take some work and experimentation.

    Matt
    12/6/12 @ 12:48 pm

    Yeah that is close to what I am trying to do. I have a spreadsheet that I would like to count how many times a keyword is used. Some cells throughout the spreadsheet have the same keyword used more then one time. For example: “Dance all day and dance all night.” this keyword dance might be used in one cell twice. I would like to write a formula that finds my keywords and counts the total used in the entirety of certain columns. Countif only counts when a single word is alone in a cell.

      12/6/12 @ 12:56 pm

      Oh, I see. I’m sure it can be done, but maybe with so much work as to not make it worthwhile. If it was me, I would just export it and open it in BBEdit and then search for the word to see how many it finds. But if you need something that updates constantly then that won’t be helpful.

    Matthew Brown
    2/17/13 @ 11:41 am

    Hi Gary. I’m struggling to make COUNTIF work with a list of dates in Numbers on the iPad. I can make it count words and numbers no problem.

    I want to know, for example, how many dates in 2010 are in my data but the count always comes back as 0. If I try to count an exact date like 1st June 2010 it will find those but I just want to count all the 2010s regardless of day and month. Any ideas?

      2/17/13 @ 12:01 pm

      Create another column next to the date. Have it check the date so it is true if in the range, false otherwise. Then COUNTIF for that column.

Comments Closed.