With help from members, I have the following formula and have added 3 colours
to the conditional formatting section, being red for the top, orange for the
middle and lastly yellow for the bottom one. Basically, any red cells
indicate persons who are out of date and need urgent training (in my
case-First Aid but could be used for other training purposes), the orange
indicates an orange cell which implies to me, 'Get my skates on and get
something organised quickly' and the yellow indicates 'Hello, I need to be
considered for training soon'

=AND(G3<>””,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3))<=TODAY())
=DATEDIF(G3,TODAY(),”m”)=11
=DATEDIF(G3,TODAY(),”m”)=10

I currently have 3 columns with the above conditions applying;
D3 to D48
K3 to K48
P3 to P30

I would like all those cells in the 3 columns to add up and show different
statistics.
For instance
In Date: Any conditional formatting cells coloured white, orange and yellow
cells, but does not include those out of date which is the pink cells. These
are to add up and placed in cell P35

Out of Date: Any conditional formatting cells coloured red are classed as
out of date (as per line one) and the 3 columns with this formula add
together to make a total in cell P36

Thanks to those who've helped so far and thanks to those in advance.
-----------------------------------------------------------------------------

Analytics based on conditional formatting is tricky. This should give you
some ideas about what to do:
http://www.cpearson.com/excel/CFColors.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Manxy" wrote:

> With help from members, I have the following formula and have added 3 colours
> to the conditional formatting section, being red for the top, orange for the
> middle and lastly yellow for the bottom one. Basically, any red cells
> indicate persons who are out of date and need urgent training (in my
> case-First Aid but could be used for other training purposes), the orange
> indicates an orange cell which implies to me, 'Get my skates on and get
> something organised quickly' and the yellow indicates 'Hello, I need to be
> considered for training soon'
>
> =AND(G3<>””,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3))<=TODAY())
> =DATEDIF(G3,TODAY(),”m”)=11
> =DATEDIF(G3,TODAY(),”m”)=10
>
> I currently have 3 columns with the above conditions applying;
> D3 to D48
> K3 to K48
> P3 to P30
>
> I would like all those cells in the 3 columns to add up and show different
> statistics.
> For instance
> In Date: Any conditional formatting cells coloured white, orange and yellow
> cells, but does not include those out of date which is the pink cells. These
> are to add up and placed in cell P35
>
> Out of Date: Any conditional formatting cells coloured red are classed as
> out of date (as per line one) and the 3 columns with this formula add
> together to make a total in cell P36
>
> Thanks to those who've helped so far and thanks to those in advance.
> -----------------------------------------------------------------------------
>
>

Thanks Ryan for the information but to be honest, I didn't understand it as I
struggle with adding lines up!
It might be easier if the formula just kept the one rule;
Out of Date: Any conditional formatting cells coloured red are classed as
out of date (as per cond. form. in line one) and the 3 columns with this
formula add together to make a total in cell P36
So basically, when the cell turns red, it adds to the total out of date in
cell P36
-------------------------------
"ryguy7272" wrote:

> Analytics based on conditional formatting is tricky. This should give you
> some ideas about what to do:
> http://www.cpearson.com/excel/CFColors.htm
>
> HTH,
> Ryan---
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "Manxy" wrote:
>
> > With help from members, I have the following formula and have added 3 colours
> > to the conditional formatting section, being red for the top, orange for the
> > middle and lastly yellow for the bottom one. Basically, any red cells
> > indicate persons who are out of date and need urgent training (in my
> > case-First Aid but could be used for other training purposes), the orange
> > indicates an orange cell which implies to me, 'Get my skates on and get
> > something organised quickly' and the yellow indicates 'Hello, I need to be
> > considered for training soon'
> >
> > =AND(G3<>””,DATE(YEAR(G3)+1,MONTH(G3),DAY(G3))<=TODAY())
> > =DATEDIF(G3,TODAY(),”m”)=11
> > =DATEDIF(G3,TODAY(),”m”)=10
> >
> > I currently have 3 columns with the above conditions applying;
> > D3 to D48
> > K3 to K48
> > P3 to P30
> >
> > I would like all those cells in the 3 columns to add up and show different
> > statistics.
> > For instance
> > In Date: Any conditional formatting cells coloured white, orange and yellow
> > cells, but does not include those out of date which is the pink cells. These
> > are to add up and placed in cell P35
> >
> > Out of Date: Any conditional formatting cells coloured red are classed as
> > out of date (as per line one) and the 3 columns with this formula add
> > together to make a total in cell P36
> >
> > Thanks to those who've helped so far and thanks to those in advance.
> > -----------------------------------------------------------------------------
> >
> >