This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(166.4, 56.00000000000001%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Hi! I'm hitting a bit of a snag with a COUNTIF formula when it comes to setting the criteria. I want to use the cell reference 'Grade Inclusions'!Q1 in this instance, but that cell contains a TEXTJOIN function as I have a couple different grades I want to include as an OR list for the criteria. When I reference the cell, this is what it creates:
With the formula putting quote marks around the {}, it means the whole thing throws an error. I could type out the OR list myself, but there is likely to be more added to each list in future, so having it auto-update from the Grade Inclusions sheet would be fantastic.
Does anyone know how to get around this?
Excel won't recognize a text string of the form {...} as an array.
It's hard to provide advice without knowing what you want to accomplish, but perhaps you can refer to the range of cells containing FY2 and LED FY2 instead.
Sorry, it's a bit of a nightmare of a formula to explain! That little chunk containing the grade list though is just totalling how many people are in those grades (aka how many times each grade shows up in a column), which I'm using as an OFFSET value. I've tried just using the range of cells as the reference, but that gives me a #REF error unfortunately.
I know I could just type out the array myself, but I'm wanting to try and "idiot-proof" (my boss' words) everything to update automatically so it can be used by colleagues who don't have as much of an understanding about Excel and who wouldn't know how to add another grade to the array if needed.
If you want more help, we'd need to know more about the setup.
Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.
I appreciate it, but I think I've worked out a solution! It's not the best, but I've just made named array constants that I've used in place of the cell reference, then at least if they need to be added to we only need to add it to the constant. Thank you though!