Count Coloured cells

[baileyb103]

Hi. I have a spreadsheet for some assessment results (See my other posts). the cells are condition formatted so that they turn red when the assessment has failed. I am trying to get a formula to count the cells that have turned red, for each individual trainee, however the normal count functions don't seem to work with conditional formatted colours. Is there anything else I could do other than just manually in-puting the figures? The document is uploaded to a Sharepoint so would a VBA work if someone else opened the document via the Sharepoint (my knowledge on VBA is practically nothing).

Many thanks in advance.

 

[FormR]

Hi, what is the conditional formatting rule? You could probably use the same logic in a COUNTIFS() formula.

 

[baileyb103]

Hi, what is the conditional formatting rule? You could probably use the same logic in a COUNTIFS() formula.

I have used =COUNTIF(B3:AO3, "Red"), however this doesn't seem to work if the cells are coloured via conditional formatting.

 

[FormR]

Hi, what does your conditional formatting rule look like? Are you using the "Use a formula" method, or "Formal only cells that contain" or something else?

Please post the full details of the rule.

 

[baileyb103]

Hi, what does your conditional formatting rule look like? Are you using the "Use a formula" method, or "Formal only cells that contain" or something else?

Please post the full details of the rule.

 

[FormR]

If it's just the ones being formatted by the first rule that you are trying to count, then you can try:

=COUNTIFS(B3:AO3, "Fail")

 

[baileyb103]

If it's just the ones being formatted by the first rule that you are trying to count, then you can try:

=COUNTIFS(B3:AO3, "Fail")

It would be the 'Fails', and between 0.1 and 64.99999, as some are practicals (pass or Fail) and some are % where 65% is the pass mark.

 

[FormR]

Then how about:

=COUNTIFS(B3:AO3, "Fail") + COUNTIFS(B3:AO3, ">0",B3:AO3,"<65%")

 

[baileyb103]

That

Then how about:

=COUNTIFS(B3:AO3, "Fail") + COUNTIFS(B3:AO3, ">0",B3:AO3,"<65%")

works for the fails but not the % grades

 

[FormR]

From the looks of your screen shot, your %'s aren't formatted as %'s - try this subtle change instead.

=COUNTIFS(B3:AO3, "Fail") + COUNTIFS(B3:AO3, ">0",B3:AO3,"<65")