SUMPRODUCT even with #N/A values (2 dimensional sumproduct formulae)

  • Hi all,


    I have yet another burning question, this time regarding sumproduct. I am having problems getting my sumproduct formula to work, as they would show me a #value error when my referred range has null values.


    Here is my sumproduct formula:


    =SUMPRODUCT((YEAR($K$3:$K$43552)=D3)*($L$3:$L$43552))/12


    I am using a two dimensional sumproduct formula, which helps me detect which data belongs to that particular year (matching the year in D3) and sum up all those values.


    Attached is a similar version of my main working file, however, i use index and match in my main working file to reference the data instead of isblank.


    Is there anyone that could help me with this? Sorry if the explanation is rather bad.


    Thanks


    ~ Hestia

  • Hello,


    In cell B3, you could test following formula :


    Code
    =IF(ISBLANK(Sheet2!B1),[B][COLOR=#FF0000]0[/COLOR][/B],Sheet2!B1)


    or if you really do not want to modify your existing formula ...


    you could use the following array formula


    Code
    =SUMPRODUCT((YEAR($A$3:$A$21)=D3)*IF(($B$3:$B$21)<>"",($B$3:$B$21),0))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi carim,


    I have tried the array formula that you have recommended, but it still provides a #value error. Could it be because the values are referenced using a formula?


    ~ Hestia

  • Hi KjBox,


    Damn thats a genius way of using SUMIF! I was actually figuring out how to fit a "YEAR" function in a SUMIF formula before, but i gave up afterwards and went to SUMPRODUCT haha. The formulae works now with your trick XD


    Thanks for the help guys!


    ~ Hestia

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!