Countif with 2 criteria

Colin Legg · #2 05-23-2011, 02:06 AM

Hi,

Quote:

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

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:

Code:

=AND(COUNTIF(G2:G17,G8)>1,NOT(ISBLANK(G8)))

If I misunderstood the G8 repeating logic then just adjust the COUNTIF() part of the formula.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Criteria in Outlook Search	Nancy Grams	Outlook	0	11-12-2009 03:03 PM
Sort by criteria: number of results	p0k	Excel	1	10-22-2009 08:33 PM
How to count cells containing data and meet certain criteria	AdamNT	Excel	1	08-11-2006 11:51 PM
Multiple criteria in SUMIF?	pumpkin head	Excel	1	02-17-2006 09:06 AM
Filtering past or future events in DRW query criteria	jvieille	Misc	1	02-11-2006 06:26 AM