Numbers formula for Number occurrence

"It says there is only 2 fives. I'm counting 13"

That's because you are using COUNT, which counts occurrences of the number 5, not the character "5"

There are only occurrences of the number 5, both in the top row.

Heres one way to accomplish your count. It could be rolled into a single formula, but the formula would be quite long. It would consist of the individual formulas in cells B2 - D4, joined by + signs, or a list of those formulas, separated by commas and placed in a SUM statement.

Main is your data entry table. All values are entered directly; the table contains no formulas.

Calc is the table that does the calculations. It contains two formulas. The one below the tables is entered in cell B2 (selected), then filled down to B4, and the set of three filled right to column C.

For copy/paste purposes, use this copy, from Calc::B2:

LEN(main::B2)−LEN(SUBSTITUTE(main::B2,$A$1,""))

The formula uses the value entered in cell A1 of Calc to determine the character (or string of characters*) to count.

The second formula is in cell B1 of Calc. It SUMs the values in the nine cells B2-C4.

SUM(B2:D4)

*An interesting result when the search term in Calc A1 is 55, the total is 8—4 in row 4 and 4 in row 4.

I've not gone into the logic of that. Could prove interesting!

Regards,

Barry

Hi TY,

Perhaps the SEARCH function will do what you want.

The SEARCH function returns the starting position of one string value within another, ignoring case and allowing wildcards.

SEARCH(search-string, source-string, start-pos)

• search-string: The string value to find.
• source-string: The string value to search.
• start-pos: An optional number value specifying the position within the string at which the action should begin. start-pos must be greater than or equal to 1 and less than or equal to the number of characters in source-string.

This problem becomes easier to solve if you copy your data from the two-dimensional array to a single column (Column A in the "Search for character" table).

I am using "5" as the search string (in the single-cell table "Character") and Column A as the source-string.

Formula in B2 (and Fill Down)

SEARCH(Character::$A$1,$A2,1)

We are getting red error triangles where SEARCH is not able to find a match.

Ignore those for now. We can use IFERROR to remove them.

Formula in C2 (and Fill Down and Fill Right as far as needed)

=SEARCH(Character::$A$1,$A2,B2+1)

B2+1 sets the start-pos for the next five if it is there. Again, a red error triangle if it is not there.

Note the $ symbols (absolute cell reference where needed).

Now wrap the formulas in IFERROR to get rid of the red error triangles

Formula in B2 (and Fill Down)

IFERROR(SEARCH(Character::$A$1,$A2,1),"")

Formula in C2 (and Fill Down and Fill Right as far as needed)

IFERROR(SEARCH(Character::$A$1,$A2,B2+1),"")

Formula in Footer cell B14

=COUNTIF(B:E,">0")

Regards,

Ian.

Hi quinn,

I'm afraid I'd be hard pressed to give a technical explanation of what the purple notation actually means. Much like a little kid, I've learned most of the language by hearing (or, in this case, reading) how other use it and doing a little "monkey see, monkey do" until I get the idea. Then, because I get confused by complexity, I boil things down to something as short and sweet as I can.

AppleScript gets a bum rap from real techies as not being very good at manipulating strings. It is a little verbose, and not a speed demon. But once one gets a feeling for text item delimiters (more than one can be used at the same time!) one quickly realizes many things are possible that seemed out of reach before!

The little script above simply reads the values in the selected cells all into one big string, splits that string into chunks using the designated delimiter (in this case "5"), then counts the resulting chunks and subtracts 1. That's it!

For me the biggest help with scripts has come from Google searches, macscripter.net, iworkautomation.com, and, when one really needs to get down in the weeds, resources on the Apple site.

SG

If you're doing counts like these a lot a script might be easier.

1. Copy-paste script into Script Editor
2. Select the cells
3. Click the "triangle" run button.

SG

-- select the range of cells and click

set countDigit to "5"

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range) to ¬

set vvStr to selection range'scells'sformatted value as text

end tell

end tell

set text item delimiters to countDigit

set numOccur to ((vvStr's text items)'s length) - 1

set text item delimiters to ""

display dialognumOccurbuttons "OK"

Hi SG,

You have me looking up "text item delimiters" online. Always great to see a script from you.

Do you know if there is a list of items that appear purple in a script? I am sure that there are useful nouns that I just don't know.

quinn

Thanks SG,

The apple site looks like it has waht I am looking for. The sites I already have bookmarked have been more "Here is a script to do such and such" and "Hello World" type examples.

quinn