=COUNTIF($G$2:$G$19,G8)=1

I want to edit the above formula to inject AND to make 2 criteria

This did not work: =COUNTIF(AND(($G$2:$G$17,G8)=1)G8 <>"")

What I want to happen is for the cell not to accept any input if G8 is not repeated and/or blank.

Hi,
I don't fully understand what you mean by not to accept any input if G8 is not repeated'. At the moment you are checking if G8 occurs once:
=COUNTIF($G$2:$G$19,G8)=1

but, since G2:G19 intersects G8, for G8 to be repeated, shouldn't the check be:
=COUNTIF($G$2:$G$19,G8)>1

?


Depending on that, I think you want a data validation formula like this:
If I misunderstood the G8 repeating logic then just adjust the COUNTIF() part of the formula.

__________________
Colin

RAD Excel Blog

Thanks, let me rephrase my problem:

I need to enter data in columns H where this formula is needed and column G where criteria has to be met. Column G for example has rows for data validation from 2 to 19:

First criteria - H should not accept the data (which is numerical) if the value in G2 until G19 is duplicated. The value in G column must only entered once

Second criteria - H should not accept the data if value of column G is blank.

I hope this is clearer now. Thanks.

=COUNTIF($G$2:$G$19,G8)=1

but, since G2:G19 intersects G8, for G8 to be repeated, shouldn't the check be:
=COUNTIF($G$2:$G$19,G8)>1

Hi,

Data validation in H2 would be:
When you enter the validation formula, untick the "Ignore Blank" checkbox.

__________________
Colin

RAD Excel Blog