Reference to range name returns #VALUE! error in Array formula

Question

I am trying to sum values in a range, meeting certain conditions, using an array formula.

The values are in columns, with ranges named by month such as "jan", "feb", "mar", etc.

Cell C6 contains a drop-down list where I can select the specific month I want. (in this case "jan")

The following formula returns a #VALUE! error:

{=SUM((segment=$B11)*(scenario=D$10)*(year=D$9)*(measure=$C$8)*C6)}

However, when I just specify the range "jan" in the formula (see below) instead of referring to cell C6, (or any other cell in which I type "jan"), the formula works well.

{=SUM((segment=$B11)*(scenario=D$10)*(year=D$9)*(measure=$C$8)*jan)}

How can I use a cell reference to a named range, instead of hard coding the named range in the formula?

Answer 1

Use INDIRECT(C60) instead of C6.

If you use only C6 you get the value in C6 as a string like "Jan" or "Feb" or ...
INDIRECT returns a reference to the value in C6 so you get named range Jan or Feb or ...

Formulas (normal not Array) used by me in C6

=SUM((Segment=$D11)*(Scenario=D$10)*(Year=D$9)*(Measure=$D$8)*(INDIRECT(D6)))

or (as I copied it over to C4 and C5 also)

=SUM((Segment=$D$11)*(Scenario=$D$10)*(Year=$D$9)*(Measure=$D$8)*(INDIRECT(D6)))

Dropdown is in D6. Segment, Scenario etc are defined Named ranges.

Note: You don't need an Array formula as you are summing up the values.

Test setup

Answer 2

However, when I just specify the range "jan" in the formula (see below) instead of referring to cell C6, (or any other cell in which I type "jan"), the formula works well.

{=SUM((segment=$B11)*(scenario=D$10)*(year=D$9)*(measure=$C$8)*jan)}

This formula does not work at all as you described your secenario.

(segment=$B11) returns a boolean, means 0 or 1

(scenario=D$10) also a boolean

(year=D$9) also a boolean

(measure=$C$8) also a boolean

jan in here is a named range and if that cell contains a text means Excel has to calculate 1*1*1*1*"jan" which is a value error.

Show your file / sample file.

Andreas.