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

Question

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

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)

Posted on Oct 21, 2012 10:05 AM

Reply

Answer 1

Barry

Level 9

59,861 points

Oct 22, 2012 9:02 PM in response to WannaBOutside

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

Reply

Answer 2

Oct 30, 2012 11:22 AM in response to Barry

Hi Barry,

Sorry for the delayed response. I thought I should let you know that I ended up changing the whole format of this particular report. I was doing something similar to what you suggested, but when the number entries started pushing past 100, the spreadsheet became extremely cumbersome to work with (10-15 seconds of wait time for every "enter" hit, three minutes to open, etc.).

You did indeed understand the question, and I did try your function suggestion, in hopes it would use less CPU than what I was doing. But I got a syntax error that indicated that the IF statement was expecting a boolean, but did not receive one. I played around with it a bit and it was working, but the delays weren't really alleviated, and then the time I had left to work on this became a factor.

Thank you for what looks like a bunch of time you put in to this.

Reply