TransWikia.com

Excel COUNTIF with non-contiguous named ranges

Super User Asked by malan88 on December 30, 2020

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:

enter image description here

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?

One 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.

Answered by malan88 on December 30, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP