SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2562998%22%20slang%3D%22en-US%22%3ECount%20cells%20in%20a%20row%20that%20has%20been%20coloured%20by%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2562998%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20cells%20that%20are%20in%20a%20row%20counted%20if%20they%20have%20a%20colour%20that%20has%20been%20set%20by%20Conditional%20Formatting%20and%20the%20number%20to%20be%20shown%20in%20a%20separate%20column.%3CBR%20%2F%3EI%20have%20uploaded%20a%20file%20that%20you%20can%20use%20and%20see%20what%20I%20am%20talking%20about%20-%20Dummy%20Test.%3CBR%20%2F%3EI%20would%20prefer%20not%20to%20have%20to%20use%20Macros.%3CBR%20%2F%3EThe%20cell%20rows%20that%20have%20Conditional%20Formatting%20are%20K6%20to%20Y6%20and%20then%20the%20rows%20below.%20The%20result%20of%20the%20count%20to%20be%20in%20J6%20and%20the%20cells%20below.%3CBR%20%2F%3EThanks%20for%20your%20help.%20Chris%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2562998%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2563204%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20cells%20in%20a%20row%20that%20has%20been%20coloured%20by%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2563204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F63805%22%20target%3D%22_blank%22%3E%40Chris%20Sewley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20repeat%20the%20logic%20of%20conditional%20formatting%20rules%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(K6%3D%22N%22)%2B%0A%20(L6%26gt%3B%3D-2)*(L6%26lt%3B1)%2B%0A%20(M6%26gt%3B%3D1)*(M6%26lt%3B%3D10)%2B%0A%20(N6%26gt%3B%3D1)*(N6%26lt%3B%3D4)%2B%0A...%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Econtinue%20the%20formula%20for%20the%20rest%20of%20columns%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2565974%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20cells%20in%20a%20row%20that%20has%20been%20coloured%20by%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2565974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3CBR%20%2F%3EI%20completed%20the%20formula%20hopefully%20correctly.%20When%20I%20put%20it%20into%20J6%20I%20got%20an%20error%20message.%20I%20have%20uploaded%20it.%20I%20cannot%20see%20where%20I%20went%20wrong.%20This%20is%20the%20completed%20formula%20that%20I%20put%20into%20the%20cell.%3C%2FP%3E%3CP%3E%3D(K6%3D%22N%22)%2B(L6%26gt%3B%3D-2)*(L6%26lt%3B%3D1)%2B(M6%26gt%3B%3D1)*(M6%26lt%3B%3D10)%2B(N6%26gt%3B%3D1)*(N6%26lt%3B%3D4)%2B(O6%26lt%3B%3D0)*(O6%26gt%3B%3D1)%2B(P6%26gt%3B%3D%26lt%3B0)*(P6%3D0.5)%2B(Q6%26gt%3B%3D-2)*(Q6%26lt%3B%3D4)%2B(R6%26gt%3B%3D-2)*(R6%26lt%3B%3D1)%2B(S6%3D%26gt%3B0)*(S6%26lt%3B%3D0)%2B(T6%26gt%3B%3D1)*(T6%26lt%3B%3D30)%2B(U6%26gt%3B%3D1)*(U6%26lt%3B%3D4)%2B(V6%26gt%3B%3D1)*(V6%26lt%3B%3D2)%2B(W6%26gt%3B%3D1)*(W6%26lt%3B%3D4)%2B(X6%26gt%3B%3D%26lt%3B1)%2B(Y6%26gt%3B%3D1)*(Y6%26lt%3B%3D2)%3C%2FP%3E%3CP%3EThanks%20for%20your%20help%20in%20sorting%20out%20my%20problem.%3C%2FP%3E%3CP%3ERegards.%20Chris%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@Chris Sewley 

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

@Sergei Baklan 

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

@Chris Sewley 

I didn't check with your file, errors are here

image.png

shall be >=0 or <=0 or like

image.png

correct is >=0

image.png

>=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)
Thanks for your help Sergei. it works great. Saves me having to physically count the cells now