Thread: COUNTIF and named ranges

Greetings!

I think I've discovered a limitation of COUNTIF but I need confirmation from the experts on this board as well as any ideas for a workaround. In a nutshell, I'm trying to use COUNTIF with a named range that consists of non-contiguous cells.

For example, I have the following named range called P1Q1 defined in Sheet1:

=Sheet1!$B$2,Sheet1!$G$2,Sheet1!$L$2,Sheet1!$Q$2,Sheet1!$V$2,Sheet1!$AA$2,Sheet1!$AF$2,Sheet1!$AK$2

As you can see this range is comprised of cells that are located in a single row but multiple, separated columns. I want to use the COUNTIF function to analyze that range and count the number of times an "X" appears. Here is the function I'm using:

=COUNTIF(P1Q1,"X")

I'm receiving this error:

"The value used in the formula is of the wrong data type."

I've seen dozens of examples out there that use a named range as the range argument in a COUNTIF function but in all of those examples the named range was a group of contiguous cells. I would not expect Excel to have trouble with this but it clearly does.

Anyone have any ideas for a workaround on this? Thanks in advance!

It doesn't use your named range, but it seems to work: =SUMPRODUCT(--(B2:AK2="X"),--(MOD((COLUMN(B2:AK2)-2),5)=0))
It ignores cells not currently in your named range, like C2.

dark...

Thanks so much for your reply, that works great!

I forgot to mention this in my original post but say I want to check for two different string values "X" or "G"? How would I modify the formula to achieve that? I'll play around with it on my end but any assistance you can provide would be much appreciated.

Replace "X" with {"X","G"}

Is this how the final formula should read:

=SUMPRODUCT(--(B2:AK2="X","G"),--(MOD((COLUMN(B2:AK2)-2),5)=0))

If so, that doesn't work. I get the generic "Your formula contains an error."

Nope, the curly brackets makes what's in them an array. It should read like =SUMPRODUCT(--(B2:AK2={"X","G"}),--(MOD((COLUMN(B2:AK2)-2),5)=0))

Sorry dark...that's not working either. Here's the formula I'm using copied verbatim from my sheet:

=SUMPRODUCT(--(B2:AK2={"X","G"}),--(MOD((COLUMN(B2:AK2)-2),5)=0))

I get the error:

"A value is not available to the formula or function"

Odd. I've seen that used so often before here in the forum. Anyway, I tested this version before posting it. =SUMPRODUCT((B2:AK2="X")+(B2:AK2="G"),--(MOD((COLUMN(B2:AK2)-2),5)=0))
Sorry for the error.

Now we're in business! Thanks again!!