collate RAG results

[lp78]

hi all and thanks in advance for any help that is offered.
I have a spreadsheet with with a 'RAG' status in column A. I would like the total number of 'reds' to populate cell Z2 and the total number of 'ambers' to populate cell Z3. Is this possible?

 

[MisterBate5]

countif()

 

[lp78]

really new to this. Can you help me a little more.

 

[JamesW]

Hi there,

Try:

=COUNTIF(A1:A6,"amber")
=COUNTIF(A1:A6,"red")

 

[GlennUK]

Are your 'red's and 'amber's actually cell contents, or are they coloured? If they are coloured, how are they coloured .... by Conditional Formatting, or by macro? Any information would be useful. Or maybe James answer covers your case.

 

[stilesbn]

In Z2 enter

=COUNTIF(A:A,"red")

In Z3 enter

=COUNTIF(A:A,"amber")

You can change "A:A" to whatever range you need. Right now it is looking at the whole column of A.

 

[lp78]

hi and thank you. The cells in Column A hold dates and are conditionally formated to turn amber when 90 days have past and turn red when 180 days have past.

 

[GlennUK]

=COUNTIF(A:A,"<"&(TODAY()-180))

will count dates more than 180 days old.

=COUNTIF(A:A,"<"&(TODAY()-90))-COUNTIF(A:A,"<"&(TODAY()-180))

will count dates between 90 and 180 days old.

 

[lp78]

hi that worked great apart from i need it only to include dates thats do not have an end date in column k. Is this possible.

 

[lp78]

hi that worked great apart from i need it only to include dates thats do not have an end date in column k. Is this possible.