Disallow duplicate entries using SUMIF

**adamwestrop** · 08-01-2011, 03:31 PM

I have Excel 2010

I am using a range or table and presented with the same problem.

I have a list of ID's in Column A, with a column header in A1.

I dont want duplicate entries to be entered.

I thought if I went into the Data Validation menu (after selecting the range in A with the exception of header A1) and put a custom list of =SUMIF(A:A,A1)=1 then when I went to put a duplicate entry within the range/table the restriction would not allow me.

However this isn't true, can someone help me with this please?

**daddylonglegs** · 08-01-2011, 03:37 PM

Try COUNTIF rather than SUMIF, i.e.

=COUNTIF(A:A,A1)=1

**BigBas** · 08-01-2011, 03:37 PM

You're close. Cell A1 is just a header, so it shouldn't be part of the validation. In the validation menu, choose the CUSTOM option, and enter the following formula, starting with cell A2:

Please Login or Register  to view this content.

**adamwestrop** · 08-01-2011, 04:33 PM

Originally Posted by BigBas

You're close. Cell A1 is just a header, so it shouldn't be part of the validation. In the validation menu, choose the CUSTOM option, and enter the following formula, starting with cell A2:

Please Login or Register  to view this content.

So fricking obvious Thanks.

Am I right in thinking this works on just tables and not ranges? No workaround for ranges?

For an ever changing list, which is added to say like 20-30 entries a day, what should I do to accommodate this? Just tab the table down all the time?