Sumif with named range using INDIRECT()

1
votes

I have a named range TYPE_NAME which is a list of types of projects. Correspondingly, a named range TYPE_VALUE contains "Yes"/"No" for each type.

Then, for each entry in TYPE_NAME, there is a corresponding man hour value stored in a named range "HC_" where is the entry in TYPE_NAME. All the HC_xxxx are scattered in various locations within my workbook.

Question: Is there a worksheet formula that can calculated the total sum of man hour for each TYPE with VALUE = "Yes"?

I tried the following array formula but it is not working. 

{=SUMIF(TYPE_VALUE, "Yes", INDIRECT(CONCATENATE("HC_",TYPE_NAME)))}

It always use the 1st entry in TYPE_NAME and sum the continuous column of HC_<1st entry> according to the TYPE_VALUE="Yes" criteria.

1
excel-formulasumifsexcel-indirect

1 Answers

1
votes

You may want to use a helper column. Assuming the Range Type_Name is in column A, with the first name in cell A2, you can use the formula

=indirect("HC_"&A2)

Then you can use a sumif against the Type_Value and the helper column.