Mac Numbers, Formula Question

[LDC134]

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.

[jim360]

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.

[LDC134]

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 ?

[jim360]

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.

[LDC134]

That totally works, thank you so very much :)