Quizzes & Puzzles2 mins ago
Mac Numbers, Formula Question
4 Answers
Hi, I'm hoping someone is a whizz on formulas on Mac Numbers and can help. What I want to do is create a formula which adds (or subtracts) a certain terms within a cell. For example the list is oranges, lemons, apples, oranges, oranges and I want it to add up every time the word oranges is used. It can either add it 1+1+1 or it can be set at a total number ie 20 and decrease every time I add 'oranges' ie start at 20 and count down every time I add another cell with oranges in so 20, 19, 18 etc.
I hope I have made this clear, and someone can help. Many thanks in advance for any assistance.
I hope I have made this clear, and someone can help. Many thanks in advance for any assistance.
Answers
Best Answer
No best answer has yet been selected by LDC134. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.I was put in mind of Excel's "countif" function -- I think the same works in numbers. If, say, your list is in Column A, then the syntax should be:
=COUNTIF(A1:A20,"oranges").
This simply counts the number of times the exact word "oranges" appears in the list, and output a figure -- eg for the list example above it would give the answer 3.
=COUNTIF(A1:A20,"oranges").
This simply counts the number of times the exact word "oranges" appears in the list, and output a figure -- eg for the list example above it would give the answer 3.
Jim360 - perfect - thank you so much for your help and for getting me brownie points with the boss. One quick additional question. If the actual cell contains multiple words ie oranges,kiwi,pear do you know how I still get it to recognise the word oranges but if the cell contains just kiwi, pear then not to count it ?
You can try the formula below:
=COUNTIF(A1:A20,"*oranges*")
In excel, at least, the * character is interpreted as a wild card, in the sense of "I don't care what the hell is exactly in the cell as long as it contains oranges somewhere". I can't say I know if this works in Numbers or not, but you could try it.
=COUNTIF(A1:A20,"*oranges*")
In excel, at least, the * character is interpreted as a wild card, in the sense of "I don't care what the hell is exactly in the cell as long as it contains oranges somewhere". I can't say I know if this works in Numbers or not, but you could try it.
Related Questions
Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.