countif - issue (no duplicates)

**NBVC** · 07-14-2011, 02:09 PM

Are you confirming with CTRL+SHIFT+ENTER and then copied down?

If yes and still an issue, can you post sample spreadsheet?

**step_one** · 07-14-2011, 04:54 PM

hello NBVC

appreciate the help.

i have attached a sample workbook (see highlight in yellow).

basically, if someone adds another city in the "Data" worksheet range E12:E75, i would like a formula to capture that?

pls see highlight in red where i have tried the "array" approach but still getting repeats.

your thoughts? thx you!

**daddylonglegs** · 07-14-2011, 05:04 PM

Try this formula in U52 copied down

=LOOKUP("zzz",IF({1,0},"",INDEX(Data!$CW$12:$CW$75,MATCH(1,INDEX(ISNA(MATCH( Data!$CW$12:$CW$75,U$51:U51,0))*(Data!$CX$12:$CX$75=E$51),0),0))))

it doesn't need "array entering"....

**step_one** · 07-14-2011, 05:15 PM

Originally Posted by daddylonglegs

Try this formula in U52 copied down

=LOOKUP("zzz",IF({1,0},"",INDEX(Data!$CW$12:$CW$75,MATCH(1,INDEX(ISNA(MATCH( Data!$CW$12:$CW$75,U$51:U51,0))*(Data!$CX$12:$CX$75=E$51),0),0))))

it doesn't need "array entering"....

thx you so much, works like a charm!