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

Copper Contributor

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