SUM, COUNTIF, and INDIRECT - Need help

Hello!

I am having difficulty with a formula I worked on....

I don't understand why one works and not the other.

This one works with a direct reference to the Named Range:

{=SUM(COUNTIF(Table_Name[Column of Numbers],Named Range))}

The one below does not, even though that indirect provides the exact name of the "Named Range". It would be useful if I could get this to work since the Named Range is different depending on column D. Otherwise I will have to manually paste in the correct Named Range for each row.

{=SUM(COUNTIF(Table_Name[Column of Numbers],INDIRECT("Name_"&SUBSTITUTE(INDIRECT("D"&ROW()),"-",""))))}

Is there a way to indirectly reference a named group of cells like this?


Thank you!