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
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
Highlighted

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