Add cell value to formula

[EssKayKay]

Hello,
I posted a similar question a while ago and have most of it working. However, I have one other related question if you please.

I have a formula that I would like to add a cell address to in the range. Here is a simplified version of the formula:

=SUMPRODUCT(--(YEAR($C$32:$C$365)=R39),$Q$32:$Q$365)

What I’d like to do is replace the 365 with the value in cell T13. Maybe this is not even possible but I thought it would look something like this (apparently I am wrong):

=SUMPRODUCT(--(YEAR(“$C$32:$C$” & Range(“T13”).value)=R39),”$Q$32:$Q$” & Range(“T13”).value)

Thanks for viewing,
Steve

 

[6StringJazzer]

There might be other ways but this is the first that comes to mind:

=SUMPRODUCT(--(YEAR($C$32:INDEX($C:$C,T13))=R39),$Q$32:INDEX($Q:$Q,T13))

 

[EssKayKay]

Thank you Jeff for your quick response. This appears to be doing what I want. I had to modify the code slightly. I added $T$13 to hold the cell and added the remainder of the function. Here‘s my final:

=IF(SUMPRODUCT(--(YEAR($C$32 : INDEX($C:$C,$T$13))=R39),$Q$32 : INDEX($Q:$Q,$T$13))>0,SUMPRODUCT(--(YEAR($C$32 : INDEX($C:$C,$T$13))=R39),$Q$32 : INDEX($Q:$Q,$T$13)),"")

I have some more testing to do but this looks really promising.

Again, much appreciated,
Steve

 

[6StringJazzer]

The key thing that makes this work is that INDEX returns a cell reference, not a value. This is transparent because if you use a cell reference where Excel expects a value, it will use the value for that reference. But here I'm using it where Excel expects a cell reference. So you can use it as part of a range.

Your initial idea could have worked if you had used INDIRECT, but my version is simpler, non-volatile, and easier to copy/paste to any location.