I have defined a few different named ranges in an Excel spreadsheet and am trying to have a SUMIF formula where I can use a dropdown to change the name that the cell is pulling.
Each named range is a list based on project status. For example, Not_to_Senior_Yet refers to anything that is "Not Started" or "In Progress".
My formula works fine when I actually type out the name of the range. For example:
=SUM(SUMIFS(DHM!$K:$K,DHM!$I:$I,Not_to_Senior_Yet))
This sums all of the items from the DHM tab that are either Not Started or In Progress.
However, if I replace Not_to_Senior_Yet with a cell reference to where that text is typed, it no longer recognizes the name and the formula returns 0. I want the formula to work such that I can select Not_to_Senior_Yet or Not_to_Manager_Yet etc. and the formula will update automatically, rather than the name having to be embedded in the formula. For example:
=SUM(SUMIFS(DHM!$K:$K,DHM!$I:$I,$F$2)) where $F$2 has a dropdown that I can change to Not_to_Senior_Yet or Not_to_Manager_Yet.
Any suggestions on how to force Excel to recognize the Name referenced in $F$2?
Excel Version is Microsoft 365 Apps for Business (Version 2109) on a Windows PC
