 SOLVED

# Count cells in a row that has been coloured by Conditional Formatting

I want cells that are in a row counted if they have a colour that has been set by Conditional Formatting and the number to be shown in a separate column.
I have uploaded a file that you can use and see what I am talking about - Dummy Test.
I would prefer not to have to use Macros.
The cell rows that have Conditional Formatting are K6 to Y6 and then the rows below. The result of the count to be in J6 and the cells below.
Thanks for your help. Chris

5 Replies

# Re: Count cells in a row that has been coloured by Conditional Formatting

You may repeat the logic of conditional formatting rules as

``````=(K6="N")+
(L6>=-2)*(L6<1)+
(M6>=1)*(M6<=10)+
(N6>=1)*(N6<=4)+
...``````

continue the formula for the rest of columns

# Re: Count cells in a row that has been coloured by Conditional Formatting

Hi Sergei
I completed the formula hopefully correctly. When I put it into J6 I got an error message. I have uploaded it. I cannot see where I went wrong. This is the completed formula that I put into the cell.

=(K6="N")+(L6>=-2)*(L6<=1)+(M6>=1)*(M6<=10)+(N6>=1)*(N6<=4)+(O6<=0)*(O6>=1)+(P6>=<0)*(P6=0.5)+(Q6>=-2)*(Q6<=4)+(R6>=-2)*(R6<=1)+(S6=>0)*(S6<=0)+(T6>=1)*(T6<=30)+(U6>=1)*(U6<=4)+(V6>=1)*(V6<=2)+(W6>=1)*(W6<=4)+(X6>=<1)+(Y6>=1)*(Y6<=2)

Thanks for your help in sorting out my problem.

Regards. Chris

best response confirmed by Chris Sewley (New Contributor)
Solution

# Re: Count cells in a row that has been coloured by Conditional Formatting

I didn't check with your file, errors are here shall be >=0 or <=0 or like correct is >=0 >=1 or <=1 or =1

This one have no syntax error, but yo shall to check the logic

``````=(K6="N")+
(L6>=-2)*(L6<=1)+
(M6>=1)*(M6<=10)+
(N6>=1)*(N6<=4)+
(O6<=0)*(O6>=1)+
(P6>=0)*(P6=0.5)+
(Q6>=-2)*(Q6<=4)+
(R6>=-2)*(R6<=1)+
(S6>=0)*(S6<=0)+
(T6>=1)*(T6<=30)+
(U6>=1)*(U6<=4)+
(V6>=1)*(V6<=2)+
(W6>=1)*(W6<=4)+
(X6>=1)+
(Y6>=1)*(Y6<=2)``````

# Re: Count cells in a row that has been coloured by Conditional Formatting

Thanks for your help Sergei. it works great. Saves me having to physically count the cells now

# Re: Count cells in a row that has been coloured by Conditional Formatting

@Chris Sewley , you are welcome