Excel COUNTIF with non-contiguous named ranges

Question

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.
Does anyone have any advice?

malan88 · Answer

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.

Excel COUNTIF with non-contiguous named ranges

One Answer

Add your own answers!

Ask a Question