Referencing Named Ranges in Excel

Question

I have named several ranges in excel and would like to use them in a formula (countif). Instead of entering the formula and changing it manually all the time, I would like to reference a cell with the name of the appropriate range. For example:

=COUNTIF(C1, A1)

and in C1 write "Range1" or "Range 2" or "Range 3". When I try to do that, however, it doesn't seem to work.

I've also tried using the "Use in Formula" from the Formula ribbon, and pasting the named range into C1 (so that C1 now has "=Range1" for example), but that didn't work.

Does anyone know if such a thing can be done in Excel? Thanks for your help!

Did you try it without the quotes? Something like =countif(Range1, Range2) — Adriaan Stander
For future reference formula questions - as distinct from VBA programming questions - are better suited to Super User — brettdj

brettdj brettdj · Accepted Answer · 2012-07-16T05:06:11

You can use INDIRECT to return the string reference in C1 as the range

=COUNTIF(INDIRECT(C1),A1)

Debra Dalgleish's site has useful information on INDIRECT here

Referencing Named Ranges in Excel

1 Answers