Count conditonally formatted cells

[prashant_a_9]

Hi I have used conditional formatting in my sheet to highlight error in formats(I have used red color), now i want to count the # of reds.

The conditional formatting fill is not like the normal fill hence I am facing an issue to count it. Can someone help me with this?

Thanks,
Prashant

 

[T. Valko]

Hi I have used conditional formatting in my sheet to highlight error in formats(I have used red color), now i want to count the # of reds.

The conditional formatting fill is not like the normal fill hence I am facing an issue to count it. Can someone help me with this?

Thanks,
Prashant

Where are these cells located and what is the conditional formatting rule that is applied to them?

 

[prashant_a_9]

I have used rules like is it a number(Isnumber), (OR(A1="X",A1="Y") = FALSE) to put conditional formats.

Basically I had data validations in my sheet but user can copy data into my sheet and data validations goes of once you copy data, so I thought I will use conditional format to higlight format errors.

 

[T. Valko]

I have used rules like is it a number(Isnumber), (OR(A1="X",A1="Y") = FALSE) to put conditional formats.

Basically I had data validations in my sheet but user can copy data into my sheet and data validations goes of once you copy data, so I thought I will use conditional format to higlight format errors.

Do you mean that some of the cells contain formula errors like #N/A, #NUM!, #VALUE! and you want to count the cells with the errors?

 

[grizz]

can you protect your sheet to stop users from pasting in you sheet

 

[prashant_a_9]

T Valko - Not exactly errors line #NA etc but I want to highlight things like if someone has copied a text in a coloumn meant for a numeric value or lets say there is column which can have only two levels yes and no and users copied some other text to it.

Grizz -I cannot protect sheet to stop users from copying since it is a requirement of the tool to allow user to copy data

 

[T. Valko]

T Valko - Not exactly errors line #NA etc but I want to highlight things like if someone has copied a text in a coloumn meant for a numeric value or lets say there is column which can have only two levels yes and no and users copied some other text to it.

Ok, this will count all the text values in the referenced range:

=SUMPRODUCT(--(ISTEXT(A2:A10)))

This will count entries that are not "Yes" or "No":

=SUMPRODUCT(--(A2:A10<>""),--ISNA(MATCH(A2:A10,{"yes","no"},0)))