Put year in Sumproduct range name

[Dave5280]

I want to read the year out of a date column, and put the year in with a range name in a sumproduct formula. Example, date column entry 2/1/2021, and =Sumproduct(2021RangeName,a1:c1). So I need the Year function to pull out the year and I have the rest of the range name to concatenate it with, but I can't get the formula to be active and it errors out. If I manually put the range name in the formula it does work. I have tried various combinations of Text function with single and double quotes and can't get it to work.

Thank you for any ideas!

- Dave

 

[mart37]

A rangename must start with a letter.

 

[Fluff]

2021RangeName is not a valid name, it must start with a letter or an underscore.

 

[Dave5280]

A rangename must start with a letter.

Thanks and my range name is before the year, so it is RangeName2021.

 

[Dave5280]

2021RangeName is not a valid name, it must start with a letter or an underscore.

Thanks and mine does start with letters as in RangeName2021.

 

[mart37]

Thi sworks:=SUMPRODUCT(INDIRECT("RangeName"&YEAR(A4)),A1:C1)

 

[Fluff]

Ok how about

=SUMPRODUCT(INDIRECT("Rangename"&YEAR(A2)),A1:C1)

 

[Dave5280]

Thi sworks:=SUMPRODUCT(INDIRECT("RangeName"&YEAR(A4)),A1:C1)

Yes, Yes! That works! Thank you!!!

 

[Dave5280]

Thi sworks:=SUMPRODUCT(INDIRECT("RangeName"&YEAR(A4)),A1:C1)

Yes! That works! Thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback.