How to count the number of occurrences of a text string in numbers?

HI BB,

Your simplest solution might be to add a new column in which you concatenate the three attributes. Use COUNTIF on this column.

My example is complicated by using a random function to decide the size (Small, Medium, Large, Xtra large), the colour (Cyan, Yellow, Magenta, blacK) and the style (Slim, Regular, Hipster), then using a formula to collect the first occurrences of each combination that actually occurs on the list. I used single letters for each possible size, colour and style only to make the example fit more easily on the screen. Full names can be used for each without affecting the formulas.

Table 1:

This is your main data table. Size, Colour and Style are entered in, columns B, C and D respectively.

These are concatenated to form a single, countable value in column E, using the formula below:

E2: B&" "&C&" "&D

B, C and D are references to the columns containing the values to be concatenated. The spaces between these values are added by the " " text strings included in the formula. The added spaces are useful to human recognition of the elements of the formula output, but are not needed by the application.

Column F contains a formula that labels the first occurrence of each possible combination, and returns null strings (which appear as 'blank') to rows containing a choice combination already noted in rows above. Ths result is used in constructing the list of all combinations actually listed in Table 1.

F2: IF(COUNTIF(E$1:E2,E2)=1,MAX(F$1:F1)+1,"")

Table 2:

Column A contains a formula that builds the list of actual choices recorded on Table 1. It uses MATCH and INDEX to determine the rows that contain first entries of each combination, and to return the listed choice from its indexed row.

A2: IF(ROW()−1>MAX(Table 1::F),"",INDEX(Table 1::E,MATCH(ROW()−1,Table 1::F,0)))

For your use, I would suggest making a fixed list of all the available combinations, sorting that list into a logical order, then entering (or pasting) the list as fixed values in column A of Table 2. This would eliminate the need for the index column (F) on Table 1, and the formula above in column A of Table 2.

Entering the list of all possible combinations would also fix the order of that list, and remove the need to sort Table 1 to establish that order if desired.

Column B contains the formula that does the actual count of orders for the various combinations.

B2: COUNTIF(Table 1::E,INDEX(Table 1::E,MATCH(ROW()−1,Table 1::F,0)))

All formulas listed are entered into the cell in row 2 of their assigned column, then filled down to the end of the table.

Regards,

Barry

brightonboots wrote:

How would I count the number of Small, black, hipsters within Sheet 1 (recording findings on sheet 2)?

COUNTIFS is useful here:

in A2: COUNTIFS(Table 1::B,"small",Table 1::A,"black",Table 1::C,"hipster")

in B2: COUNTIFS(Table 1::B,"large",Table 1::A,"black",Table 1::C,"wide")

SG

This answer is super helpful, except I need to complicate this a bit more. I have 3 columns in which I need information (ie. Size, Color, cut). How would I count the number of Small, black, hipsters within Sheet 1 (recording findings on sheet 2)?

Column breakdown:

AN - Color

AO - Size

AP - Cut

SYU,

The function to do that is COUNTIF. Go to Numbers Help, Forumlas and Functions Help for examples and syntax assistance.

For example, to count all the instances of "Yes" in the body cells of Column A, write:

=COUNTIF(A, "Yes")

Jerry