Home

Count Conditional Formatted Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-393264%22%20slang%3D%22en-US%22%3ECount%20Conditional%20Formatted%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393264%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20formula%20in%20Column%2FRow%20P3%2C%20P4%2C%20and%20P5%20that%20will%20count%20the%20number%20of%20conditionally%20formatted%20cells%20in%20Column%20I3%3AI24%20based%20on%20the%20color%20of%20the%20cell.%26nbsp%3B%20%26nbsp%3BI%20have%20attached%20an%20example%20of%20my%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECoady%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-393264%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393366%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Conditional%20Formatted%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393366%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F312879%22%20target%3D%22_blank%22%3E%40CoadyKrZY%3C%2FA%3E%20%2C%20I'd%20change%20a%20bit%20formulas%20for%20conditional%20formatting%20rules%20to%20exclude%20blanks%2C%20like%20(for%20red)%3C%2FP%3E%0A%3CPRE%3E%3DAND(E3%2B5%20%26lt%3B%20TODAY()%2CLEN(E3)%26gt%3B0)%3C%2FPRE%3E%0A%3CP%3ERelative%20counting%2C%20as%20in%20previous%20post%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%24E%243%3A%24E%2424%2B5%20%26lt%3B%20TODAY())*(LEN(%24E%243%3A%24E%2424)%26gt%3B0))%3C%2FPRE%3E%0A%3CP%3ESimilar%20for%20another%20colors%2C%20added%20in%20attached%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20341px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F101448iC180664BA68F7B77%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOf%20course%2C%20that's%20only%20one%20of%20variants.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393341%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Conditional%20Formatted%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F312879%22%20target%3D%22_blank%22%3E%40CoadyKrZY%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20agree%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F312898%22%20target%3D%22_blank%22%3E%40jurgentas%3C%2FA%3E.%26nbsp%3B%3C%2FP%3E%3CP%3ETest%20the%20conditions%20that%20lead%20to%20the%20formatting.%26nbsp%3B%20Instead%20of%20performing%20the%20calculations%20cell%20by%20cell%20with%20relative%20references%20you%20will%20need%20the%20calculation%20to%20use%20entire%20columns%20as%20arrays%20and%20to%20sum%20the%20result.%26nbsp%3B%20In%20order%20to%20implement%20a%20row-by-row%20AND%20condition%20use%20the%20product%20%22*%22%20of%20the%20two%20parts%20of%20the%20condition%3B%20also%20convert%20any%20TRUE%2FFALSE%20to%201%2F0%20before%20summing.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-393298%22%20slang%3D%22en-US%22%3ERe%3A%20Count%20Conditional%20Formatted%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-393298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F312879%22%20target%3D%22_blank%22%3E%40CoadyKrZY%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIsn't%20it%20a%20better%20idea%20to%20use%20the%20condition%20that%20gives%20the%20cells%20the%20color%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
CoadyKrZY
Occasional Visitor

I am trying to create a formula in Column/Row P3, P4, and P5 that will count the number of conditionally formatted cells in Column I3:I24 based on the color of the cell.   I have attached an example of my spreadsheet.

 

Thanks

 

Coady

3 Replies

@CoadyKrZY 

Isn't it a better idea to use the condition that gives the cells the color?

@CoadyKrZY 

I agree with @jurgentas

Test the conditions that lead to the formatting.  Instead of performing the calculations cell by cell with relative references you will need the calculation to use entire columns as arrays and to sum the result.  In order to implement a row-by-row AND condition use the product "*" of the two parts of the condition; also convert any TRUE/FALSE to 1/0 before summing.

@CoadyKrZY , I'd change a bit formulas for conditional formatting rules to exclude blanks, like (for red)

=AND(E3+5 < TODAY(),LEN(E3)>0)

Relative counting, as in previous post, like

=SUMPRODUCT(($E$3:$E$24+5 < TODAY())*(LEN($E$3:$E$24)>0))

Similar for another colors, added in attached file

image.png

Of course, that's only one of variants.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies