Counting Red Text Colour, When Applied by Conditional Formatting

[Antligen]

Hello,

I have a spreadsheet that is used for testing equipment. In the G column it has a list of the expiry dates for the equipment to be tested.

I've used Conditional Formatting to change the font to Orange (when it will expire in 30 days or less) and Red (when a service is overdue.)

This are the conditional formatting formulas:

Please Login or Register  to view this content.

&

Please Login or Register  to view this content.

What I want to do is to count all of the items that have turned red. So I can see how many will need to be tested. This is going to be a big spreadsheet so I can't just count manually and risk missing things out.

Is there anyway of achieving this, preferrably without VBA?

My sample spreadsheet wont attach for some reason so will try again later!

Any help would be amazing!

[:) Sixthsense :)]

Use Countif() function

=COUNTIF(A:A,NOW()+30)

=COUNTIF(A:A,NOW())

Or

=COUNTIF(A:A,NOW()+30)+COUNTIF(A:A,NOW())

Or

To count >=now() and <=now()+30

=COUNTIFS(A:A,">="&NOW(),A:A,"C="&NOW()+30)

[Richard Buttrey]

Hi,

I can't see how either of your CFs are working. NOW() returns a decimal number that can be interpreted as a date & time.

You would need to use

=A1=TODAY() ' for red and
=A1<TODAY+30 ' for orange

in that order if you want to work with just the date.

For your count of the red cell then simply use the same logic as the conditional format setting, i.e.
=COUNTIF(A:A,TODAY())

[Antligen]

Sorry,

I didn't copy the conditional formatting properly

It is done as follows:

Format only cells that contain - Cell Value - Less than - =NOW()

and the same with the other, just with =NOW()+30

Does that change anything you have previously stated?

I've also just tried both formulas offered and the answer comes back as 0.

[Richard Buttrey]

Hi,

With the two CFs I gave you you should be using the 'Formula is.....(last) option.

In addition and as I said, use TODAY() rather than NOW()

[Antligen]

Again, not working

I'm sure it's me, naturally, but perhaps it has something to do with the formula stating "A1" in?

The column that contains the dates that need to change to red are G7:G17.

If possible could you rephrase the formula so that I can see how best to write it?

Many thanks

[Richard Buttrey]

Hi,

Upload the workbook and I'll change it for you.

[Antligen]

tester spreadsheet.xls

Here is a tester spreadsheet with my Conditional Formatting as well.

[Richard Buttrey]

Hi,
See attached.

You were still using the Cell Value option rather than the Formula option.

[Antligen]

Ok I can see how you've used the formula option... but where is the countif formula? I've tried using the formula you gave:

Please Login or Register  to view this content.

However the answer still comes back as 0?

[Richard Buttrey]

Have you tried

=COUNTIF($F:$F,"<="&TODAY())

if you want the count of all dates which are <= today rather than just equal to today.