AnswerBun.com

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:

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?

Super User Asked by malan88 on December 30, 2020

1 Answers

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!

Related Questions

Mouse on windows only works properly with USB hub

0  Asked on November 16, 2021 by chubby-unicorn

       

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

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

 

Make internal card reader readable to BIOS

1  Asked on November 14, 2021 by starkers

       

Turn on mobile hotspot on startup Windows 10

2  Asked on November 14, 2021 by george-dimitriadis

     

Router technologies to bypass NAT?

1  Asked on November 14, 2021

     

How to troubleshoot miracast on windows 10 laptop?

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

         

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

3  Asked on November 14, 2021 by user1066981

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.