## How do I count the number of occurrences of a string within a group of cells?

330 Views 2 Replies Latest reply: Oct 30, 2012 11:22 AM by WannaBOutside
Calculating status...
Currently Being Moderated
Oct 21, 2012 10:05 AM

Hello all, I'm trying to figure out how to count the number of times a child has completed certain tasks.  Here is a sample of the data (it is highly simplified here, but contains what I hope is needed to answer my question):

Unnamed Table

 Objective John Ann Alex Dave Eric 20a 20b 20c x 20a 20b 20c x 20a 20b 20c x x 19b 20a x x 20c 21b 22 x

What I am trying to do is count the number of times each child completed each objective - but I can't figure out how to go about splicing up the "objectives" fields for counting while still being able to compare them to whether or not the "child" has 'an x in the box' in that particular row.

Mac OS X (10.6.8)
• Level 7 (28,815 points)

If I read your example correctly, John's counts should be 2 for 20a, 1 for 19b, 20b and 20c, and 0 for any other objectives listed.

Here's an example that will work for your data set, assuming that any objective ma occur only once in each row of column A of the Main table. I've added an a to objective 22, but I think it is unnecessary, provided there is no objective 122, 221, etc.

This uses a two step process.

The table AUX extracts the objectives completed by each student, using the checkboxes in Main. Note that it also adds a space at the beginning and end of each string. This provides an extra character before the first objective code and after the last objective code in that row, used by the wildcard specification in the formula in the Summary table.

Formula:

Aux::B2: =IF(OFFSET(Data :: \$A\$1,ROW()-1,COLUMN()-1)," "&OFFSET(Data :: \$A\$1,ROW()-1,0)&" ","")

Fill right to the last column and down to the last row of Aux.

Summary uses COUNTIF to count the number of occurrences of each of the objectives listed in column A of that table.

Formula:

Summary::B2: =COUNTIF(Aux :: B,"=*"&\$A2&"*")

Fill right and down as the previous formula.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.