If Statement within conditional formatting

Morning happy campers!

Currently i have a CF set up as

=$C2>$C$1

which applies to a whole host of cells, essentially, if the figure in C2 is greater than in C1, the row changes colour.

Column A - Name
Column B - Date
Column C - *formula*

=TODAY()-B2

(this continues down the sheet)

I would like to add a CF that would look at the name in Column A and do the following:

If the name in A2 onwards is duplicated AND the number in C2 onwards is LESS than C1, turn the line blue.

Many thanks in advance

Galvinpaddy

Hi and thanks for the incredibly quick reply!

I have added the formula but it does not change the colour.
edit - i have set the format to change colour

\1

Hi Fotis,

First off, thank you to JosephP for the intitial helper, and thankyou to fotis for the additional.
However -

Having looked at the attachment you sent, i cant work it out

could you please modify the formula?
where your sheet reads -

=AND(COUNTIF($A$2:$A$1000,$A3)>1,$C3<$C2)

Which looks at C3 being less than C2 - not an issue as i can change that to $C2<$C$1.

Where i am struggling with is:

When the name is duplicated AND the number is lower than the value in $C$1, i would like both instances of the row highlighted in blue, but no line would be highlighted if both are not under the value in C1

I have attached the file to help explain.
Thanks again

Hi, so sorry, i do have a tendancy to not explain enough

Ok, row3 is the same name as row5, BUT both the numbers are not below the value of C1 (90) so i dont want either of them to be highlighted.
However, if 2 identical names exist and both numbers are under the value of C1 (90) then i would like both highlighting.

Thanks, and sorry, again

Edit - Sorry!!!
I inserted a column next to A, ran

=trim(A2)

and copied all the way down, and now the conditional Format works, the question now is how can i stop that happening to blank rows?

Kind regards

Good morning,

I have tried the formula supplied but am still unable to get the required result.

For example, in my worksheet i have the same name appear 5 times in Column A, in Column C the values are 138, 110, 97, 66, 12. Yet all are highlighted in blue.
The CF i have entered is

=SUMPRODUCT(($A$2:$A$1000=$A2)*($C$2:$C$1000<$C$1))>1

And in the section where you define what area the formula applies to i have added

=$A$2:$J$999

Another side effect is that all rows currently blank within the range A2:J999 are now also blue.

hi,
I believe that does the trick, thank-you very much for your support.

Much appreciation to you both