Hello,
I've build an array with (among more) dates and amonts. In the date list there is a formula, namely when the date matches a certain condition, then this date becomes blank (being "").
The intention is to show the last amount of a certain year. This amount can be in March but can also be in September or December, for example.
The dates are in column A, the amounts in column F. The condition year is in cell J11.
So, when I want to find the amount, I'm using the following formula:
SUMPRODUCT(--(A12:A500=MAX(IF(YEAR(A11:A500)=J12;A11:A500)));F11:F500)
However, everytime, I received as a result #VALUE!
I've tried several ways and noticed that the following formula works very well: SUMPRODUCT(--($A$11:$A$23=MAX(IF(YEAR($A$11:INDIRECT(ADDRESS(500-COUNTBLANK($A$11:$A$500);1)))=J11;$A$11:INDIRECT(ADDRESS(500-COUNTBLANK($A$11:$A$500);1)))));$F$11:$F$23) The problem however is that in this example the blank is in cell A24 and then I'm getting this #VALUE! error.
How can I solve this?
Thanks a lot!
I've build an array with (among more) dates and amonts. In the date list there is a formula, namely when the date matches a certain condition, then this date becomes blank (being "").
The intention is to show the last amount of a certain year. This amount can be in March but can also be in September or December, for example.
The dates are in column A, the amounts in column F. The condition year is in cell J11.
So, when I want to find the amount, I'm using the following formula:
SUMPRODUCT(--(A12:A500=MAX(IF(YEAR(A11:A500)=J12;A11:A500)));F11:F500)
However, everytime, I received as a result #VALUE!
I've tried several ways and noticed that the following formula works very well: SUMPRODUCT(--($A$11:$A$23=MAX(IF(YEAR($A$11:INDIRECT(ADDRESS(500-COUNTBLANK($A$11:$A$500);1)))=J11;$A$11:INDIRECT(ADDRESS(500-COUNTBLANK($A$11:$A$500);1)))));$F$11:$F$23) The problem however is that in this example the blank is in cell A24 and then I'm getting this #VALUE! error.
How can I solve this?
Thanks a lot!
|