Counting Cells with Conditional Formatting

[jbanks]

Hi

I have read various threads regarding this but due to a lack of VBA knowledge I get lost in them! Therefore, I'm hoping by explaining my situation, it may make more sense to me -
So here goes!

I work as a teacher and my role is the analysis of data, something I can do fairly easily using my Excel knowledge but I want to take it a step further (maybe this isn't the best was and another suggestion would be great.

The spreadsheets I use have 400 students in columns A (first name) and B (last name), in columns C to AC I have the students targets for the 26 subjects/courses we offer, a student would only study between 10 and 14 of these so in a row there would be blanks. In cells AE to BE I have the students current grades (those which show current situation/progress). The first student would be in ROW 2.

I want to show whether a student is below, equal to or exceeding their target and have done this using Conditional Formatting (3 separate conditions) using RED for below, White for Equal to and Green for Exceeding.

I now want to count how many of each colour there are to quickly work out how many of the subjects the students are falling behind in so we can focus our efforts on these.

Any help would be great (please keep it straightforward) and if you can link it to my cell references that'd be even better!

I can supply an exert if this would make it easier!

Thanks ever so much for any suggestions (of this isn't the best way!) or any specific help!

A. Teacher!

 

[VoG]

The easiest way is to use a COUNTIF formula using the same criteria that you applied for your CFs.

 

[jbanks]

Thanks for your reply!

I am not sure if I can use the same Conditions to do a COUNTIF as a child may have a Target of a B in English and A in Maths, however their current progress grades could be both Bs therefore one would be shaded WHITE (equal to Target) and the other RED (below Target), unless I have misunderstood how I could use COUNTIF in this example. If you could explain further that would be great (its late in the UK!)

Also in Work I use Office 2007 so any solutions using that would help too!

Thanks again!

J

 

[VoG]

Unless you can use your original conditions then this is not straightforward. See http://www.cpearson.com/excel/CFColors.htm for a possible solution.

 

[jbanks]

Thanks again!

I've sen that when I have searched previous threads but due to my lack of VBA I can't work it out!

So I'm a bit lost with it!

 

[Scott Huish]

There are limits to the code that Vog II pasted a link to. Depending on what you are doing will determine whether that may help or not.

What are your exact conditions you are using in Conditional Formatting?

 

[xld]

There are limits to the code that Vog II pasted a link to. Depending on what you are doing will determine whether that may help or not.

What are your exact conditions you are using in Conditional Formatting?

THis doesn't have those limitations

http://www.xldynamic.com/source/xld.CFConditions.html