SUMPRODUCT and blanks gives #VALUE!

[Eddyke]

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!

​

 

[Domenic]

Your ranges need to be the same size. Assuming that your data starts at Row 11, try...

=SUMPRODUCT(--(A11:A500=MAX(IF(YEAR(A11:A500)=J12;A11:A500)));F11:F500)

Hope this helps!

 

[Eddyke]

Your ranges need to be the same size. Assuming that your data starts at Row 11, try...

=SUMPRODUCT(--(A11:A500=MAX(IF(YEAR(A11:A500)=J12;A11:A500)));F11:F500)

Hope this helps!

Hi Domenic,

Thanks for your reply!

Just tried it ... but unfortunately this doesn't work. I still got the #VALUE! error. The problem is that the formula works until the date is a blank (""), after that, I'm getting the error.

Best regards,

 

[Domenic]

In that case, try...

=SUMPRODUCT(--(A11:A500=MAX(IF(ISNUMBER(A11:A500);IF(YEAR(A11:A500)=J12;A11:A500))));F11:F500)

Hope this helps!

 

[Eddyke]

In that case, try...

=SUMPRODUCT(--(A11:A500=MAX(IF(ISNUMBER(A11:A500);IF(YEAR(A11:A500)=J12;A11:A500))));F11:F500)

Hope this helps!

Great!
This works.
Thanks a lot!!

 

[Domenic]

You're very welcome, glad I could help.

Cheers!

 

[Joe4]

Note that in the futute when marking posts as the solution, please post the post that first answered the question, not your post acknowledging that some other post is the solution.
I have updated this for you on this thread.

Thanks