Home

How to summarise coloured cells which are determined by conditional formatting?

%3CLINGO-SUB%20id%3D%22lingo-sub-933762%22%20slang%3D%22en-US%22%3EHow%20to%20summarise%20coloured%20cells%20which%20are%20determined%20by%20conditional%20formatting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-933762%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20excel%20document%20which%20tracks%20how%20close%20a%20product%20is%20to%20it's%20expiry%20date%20by%20means%20of%20a%20red%20amber%20and%20green%20system.%20Conditional%20formatting%20is%20applied%20based%20on%20where%20todays%20date%20is%20relative%20to%20H%20-%20N.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20summary%20sheet%2C%20there%20are%202%20columns%20for%20a%20green%2C%20amber%20and%20red%20summary.%20The%20first%20column%20I'd%20like%20the%20%25%20of%20each%20colour%20for%20that%20particular%20product%20for%20todays%20date%20for%20the%20next%203%20weeks.%20The%20second%20column%20will%20the%20summarise%20the%20%25%20colour%20for%20each%20product%20for%20week%203%20to%20week%206.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20reading%20mixed%20things%20online%20whether%20I%20have%20to%20use%20VBA%20(%20I%20don't%20know%20how%20to%20use%20this).%20Can%20anyone%20give%20me%20a%20hand%20please%2C%20I'd%20really%20appreciate%20it%20as%20I'm%20a%20little%20stuck%20on%20what%20to%20do.%20Please%20see%20the%20attached%20example.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdrive.google.com%2Ffile%2Fd%2F12hI5ko9qYIwwJm1YG0o-s5aKY1xY-3o8%2Fview%3Fusp%3Dsharing%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ELink%20to%20example%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-933762%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-934763%22%20slang%3D%22en-US%22%3ERE%3A%20How%20to%20summarise%20coloured%20cells%20which%20are%20determined%20by%20conditional%20formatting%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-934763%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20keep%20it%20simple%20I'd%20add%20a%20series%20of%20helper%20columns%20%2F%20formula%20that%20mimic%20the%20conditional%20formatting%20rules%20returning%201s%20and%200s%20then%20refer%20to%20those%20cells%20to%20do%20your%20summary%20calculations%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
GH8568
Occasional Visitor

I have an excel document which tracks how close a product is to it's expiry date by means of a red amber and green system. Conditional formatting is applied based on where todays date is relative to H - N.  

 

On the summary sheet, there are 2 columns for a green, amber and red summary. The first column I'd like the % of each colour for that particular product for todays date for the next 3 weeks. The second column will the summarise the % colour for each product for week 3 to week 6. 

 

I'm reading mixed things online whether I have to use VBA ( I don't know how to use this). Can anyone give me a hand please, I'd really appreciate it as I'm a little stuck on what to do. Please see the attached example. Link to example 

1 Reply

To keep it simple I'd add a series of helper columns / formula that mimic the conditional formatting rules returning 1s and 0s then refer to those cells to do your summary calculations

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
32 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies