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.

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

Alice Cooper
13 years ago

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

Michael
12 years ago

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).

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

12 years ago

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

Al
12 years ago

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 years ago

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 years ago

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 years ago

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 years ago

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

Matt
12 years ago

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

12 years ago

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 years ago

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 years ago

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
12 years ago

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?

12 years ago

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.