Using dynamic named ranges, data validation, and SUMIFS together.

[Houman]

Dear Mr. Excel Community,

I hope that all is well. I’m looking to you for some help using named ranges, data validation, and the sumifs function together.

Here is a simplified version of my problem:

I have two columns: Column A has letters X, Y, and Z in the first three rows and column B has the numbers 1, 2, and 3.

I have named the range (A1:A3) “Letters”.

In cell C1 I have used data validation to allow a list of named ranges, including “Letters”.

In cell D1 I’m using the formula:

=SUMPRODUCT(SUMIFS(B:B,A:A,INDIRECT(C1))

To find the sum of the B column (Using the SUMPRODUCT and INDRECT is the only way I can get the formula to work).

Everything is rosy so far.

Now if I change the name range to a dynamic one inserting:

=OFFSET($A$1,0,0,COUNTA(Lists!$A:$A),1)

In the “Refers to” box of the Name Manager for “Letters”, the SUMIFS formula above stops working.

The dynamic range itself seems to be correct, it is the INDRECT referencing using the data validation cell that is somehow breaking things down.

Ideally I’d like to avoid using macros.

Any help would be truly appreciated.

Best,
Houman