Jul 19 2021 09:08 AM
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
Jul 19 2021 09:44 AM
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
Jul 20 2021 06:49 AM
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
Jul 20 2021 09:00 AM
SolutionI 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)
Jul 21 2021 12:49 PM
Jul 22 2021 02:16 AM
@Chris Sewley , you are welcome
Jul 20 2021 09:00 AM
SolutionI 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)