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!
Bookmarks