Cell changing colour depending upon value

The first part is just basic Conditional Formatting.

For the formula:

Formula:

Please Login or Register  to view this content.

thank you so much.
I get the bit about conditional formatting but how do I get the cell c3 to change colour based upon the values in cell c10 please?

Using conditional formatting, select the cells C3:H3 and apply the following rules (all the way below). By the way, merging cells is a very undesirable practice.

Values as displayed

	C	D	E	F	G	H	I
3	1	2	3	4	5	6
4
5
6
7
8
9
10	Yes	No	Yes	No	Yes		3/6
Sheet1

Underlying formulas

	C	D	E	F	G	H	I
3
4
5
6
7
8
9
10							=COUNTIF(C10:H10,"Yes")&"/"&COUNTA(C3:H9)

Conditional Formatting Rules

Formula
Applies To	Formula	Format	Stop If True
$C$3:$H$9	=C10="Yes"		False
$C$3:$H$9	=C10="No"		False
Sheet1

Do you really mean how to exclude counting any cells that are blank in row 4? You are using COUNTA in row 3, which counts non-empty cells. But all the cells have values. You could use COUNTA on row 4 instead.

Values as displayed

	C	D	E	F	G	H	I	J
3	1	2	3	4	5	6	7
4	Yes	Yes	No		Yes	Yes	Yes	5/6
Sheet1

Underlying formulas

	C	D	E	F	G	H	I	J
3
4								=COUNTIF(C4:I4,"Yes")&"/"&COUNTA(C4:I4)

Hi again thank you for this.
Apologies I should have been more clearer. So in the attached spreadsheet in cell F3 there is an 'X' But id like to exclude that from the total in cell J4 please...

back2thefuture=test (1).xlsx

The formula I provided will give that answer whenever there is a blank in row 4 instead of "Yes" or "No". Does it matter what is in row 3?

Originally Posted by back2thefuture

So in the attached spreadsheet in cell F3 there is an 'X' But id like to exclude that from the total in cell J4 please...

If you want to ignore "Yes" in F4:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Are you still using Excel 2019?

This will ignore the X:

=COUNTIF(C4:I4,"Yes")&"/"&COUNT(C3:I3)

COUNT will count only numbers, not text, and the result will be 5/6 - is that what you want?

Could anyone help please

How should Excel know that F and H are to be ignored based on this???

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D	E	F	G	H	I	J	K
4									0/0

Sheet: Sheet1

What happened to this layout? Wny do you keep changing the requirements???

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D	E	F	G	H	I	J
3	1	2	3	X	5	6	7
4	Yes	Yes	No		Yes	Yes	Yes	5/7

Sheet: Sheet1

Utterly confusing, but maybe try this:

=COUNTIF(C4:I4,"Yes")&"/"&COUNTA(C4:I4)

If this is what you mean:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D	E	F	G	H	I	J	K
3
4	No	Yes	Yes		No		Yes	Yes	4/6

Sheet: Sheet1

then this:

=COUNTIF(C4:I4,"Yes")&"/"&COUNTA(C4:I4)

Otherwise I really don't know what you expect. Excel can't distinguish one empty box from another and it can't count colour formatted cells by formula, either.

It would be helpful if you'd hang around for a while after bumping a thread to respond to any queries.

Thanks Ali. Should it be this instead:

=COUNTIF(C4:J4,"Yes")&"/"&COUNTA(C4:J4)

Probably, if that's the range you want to look at - but I don't know why you are asking me when YOU keep changing the requirements!

Does it do what you want?

If so, glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.