# Excel COUNTIF with non-contiguous named ranges

I am using a formula for Data Validation to hide used names like this: =IF(COUNTIF(DrawUsed, [Players])>=1, "", [Players]), where DrawUsed is a named range. The problem is that my table looks like this:

I need the Blind Draw Team columns to be the first parameter of the COUNTIF, since if a name is used in the first column, it should not be available for the third Blind Draw Team column. The issue is that this formula, with a named range defined =Blind!$B$2:$B$27,Blind!$I$2:$I$27,Blind!$P$2:$P$27 results in nothing but #VALUE! errors.

I cannot define a contiguous range because the Buyer columns should not be dependent on the values of Blind Draw Team, and should not hide used names, either.

Super User Asked by malan88 on December 30, 2020

I solved this by defining 3 separate ranges and this formula:

=IF(
OR(
COUNTIF(BlindUsed1, [Players])>=1,
COUNTIF(BlindUsed2, [Players])>=1,
COUNTIF(BlindUsed3, [Players])>=1
),
"",
[Players]
)

Returns for legibility's sake.

Update

Solution 2: INDIRECT can handle non-contiguous ranges. I had a range that was literally just a bunch of noncontiguous cells. I defined it like this:

=INDIRECT(
{
"Sunday!$F$7",
"Sunday!$F$15",
"Sunday!$F$23",
"Sunday!$F$31",
"Sunday!$F$39",
"Sunday!$M$7",
"Sunday!$M$15",
"Sunday!$M$23",
"Sunday!$M$31",
"Sunday!$M$39",
"Sunday!$T$7",
"Sunday!$T$15",
"Sunday!$T$23",
"Sunday!$T$31",
"Sunday!$T$39"
}
)

Then COUNTIF works on the range: =COUNTIF(SundayCaptains,[Player]). The only con with this method is the values won't update if you rename the worksheet.

Answered by malan88 on December 30, 2020

## Related Questions

### Chrome, Safari, and Airmail have stopped trusting random certificates, including ones for Google-owned sites

1  Asked on November 16, 2021 by tubedogg

### Avoid automatically switching to other virtual desktop when opening a file

2  Asked on November 16, 2021 by yaman

### Mouse on windows only works properly with USB hub

0  Asked on November 16, 2021 by chubby-unicorn

### How do I fix WHS 2011 failing to install KB4535102; return code 80092004?

1  Asked on November 16, 2021 by gollyjer

### PC startup issue: Can a video card short damage a motherboard?

1  Asked on November 16, 2021 by jeffrey-g-jarjoura

### USB Wifi Adapter connected to Kali Vbox but absent in ifconfig

1  Asked on November 16, 2021 by ahmed-mustafa

### Cloning windows 10 to SSD NVMe without a reader

0  Asked on November 16, 2021 by veoxer

### Converting wma to mp3 in bulk without changing directory

6  Asked on November 14, 2021

1  Asked on November 14, 2021 by starkers

### Router technologies to bypass NAT?

1  Asked on November 14, 2021

### How to list all Privileges held by a Process?

2  Asked on November 14, 2021

### How to troubleshoot miracast on windows 10 laptop?

1  Asked on November 14, 2021 by jonesome-reinstate-monica

### Boot a physical disk with EFI in VirtualBox Ubuntu 18.04 host

2  Asked on November 14, 2021 by supermechacow

### How to change “Volume Serial Number” in Windows docker image?

1  Asked on November 14, 2021 by erik-ovegrd

### Supermicro X9DRL-iF doesn’t boot anymore. Stuck on code B2

1  Asked on November 14, 2021 by brunobhr

### How to set +60Hz refresh rate with an Intel CPU?

3  Asked on November 14, 2021 by user1066981

### Microsoft Edge: where to manually set registry settings for policy testing?

1  Asked on November 14, 2021 by ag1

### GNUCOBOL with embedded sql runtime error : module ‘OCESQLConnect’ not found

2  Asked on November 14, 2021 by shooter