Search cells based on student name, cell background colour and return a tally for colour

At my school we're provided with national test data for entire year levels of students. Our task is to isolate some data for our own classes to hopefully improve student outcomes.

I've attached a spreadsheet which shows the type of format we're presented with. I just don't know how to calculate tallies based on cell background colours for just my class.

Explanation:
I have a class summary worksheet which lists individual students in my class (generally up to 30 students). On this same class summary sheet there are some cells to show tallies of achievement levels. These show how the class performed at the national test in reading, writing and numeracy.

The Year Level Summary worksheet has the results for the entire year level (usually over a hundred students). There are columns for student results in grade 3, 5, 7 and 9. This group only has results from grade 3 and 5.

There are five possible cell background colours from green to red. I'd like to search through the hundred or so students, finding the students in my class and returning the tally of each colour for their reading, writing and numeracy (this would be from their grade 5 test as it was the most recent test).

I've used the following formula to find colour tallies for the entire year level but I'm not sure how to produce a tally for just my class.

=CountCcolor('Year Level Summary'!E9:E107,'Year Level Summary'!H113)

The CountCcolor function (thanks to Raddini Rahayu):

Please Login or Register  to view this content.

I hope this makes sense and I'd really appreciate any help anyone can give.

Thank you.

This Macro Creates a new sheet listing just your class

Please Login or Register  to view this content.

Hi, drof_06,

according to Forum Rule 3 you would need to add code-tags to your procedure. Thanks in advance.

Ciao,
Holger

I don't know what workbook you tried my macro in.

it works perfectly in your sample.

The error would occur if none of your students names appeared in the Year Summary.

Run Macro1 in the attached workbook.