Work-around for COUNTIFs 255-character limit?

I am trying to find a work around to the 255-character limit for the COUNTIF function.

Background: I work at a school and I am developing a spreadsheet that the teachers can use as a substitute for their paper lesson plan books.

For each class period, the teacher enters relevant information on one worksheet (worksheet.Q4 in the sample file). This information includes the state Standards that are taught for each lesson. The cells where teachers identify the standards are highlighted yellow in the sample file. To identify the Standards, the teacher clicks the drop-down list once to choose the area of emphasis (“Strand”), then clicks again to choose from the list of relevant standards.

The other worksheet (worksheet.Standards) provides the Strands and Standards used in the drop-downs/data validation lists. I want to show which Standards have been taught and which remain. I used the COUNTIF function to find the number of times each Standard appears on the Q4 worksheet by grade level.

This works perfectly for Standards that are less than 255 characters. My problem is that there are a number of Standards that are longer.

I have tried various combinations of SUBSTITUTE, RIGHT, SEARCH, etc., within the COUNTIF function without success. The discriminating text between similar standards will be at the end of the text string, thus the RIGHT function.

I need a cross-application/cross-platform solution, because I have teachers who use Excel 2003 and Excel for Macs 2004. I would prefer to stay away from using a macro because macros are usually blocked by our workstation permissions. I don’t want to use a pivot table because I want to limit user intervention. I also do not want to edit the text of the Standards because those are verbatim from the state. I am sure that there is a formula solution that I am just not seeing.

Thanks for your ideas and help!

Perhaps try:

=SUMPRODUCT(--('Q4'!B$2:B$269=$I2))

copied down

Here is a good start: Sumproduct