Currently Being ModeratedOct 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.
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.
Summary::B2: =COUNTIF(Aux :: B,"=*"&$A2&"*")
Fill right and down as the previous formula.
Currently Being ModeratedOct 30, 2012 11:22 AM (in response to 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.