Sumproduct returns 0

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    Hi all,


    I'm running into an issue with the SUMPRODUCT formula returning 0 instead of the actual values.


    Code
    1. =SUMPRODUCT(SUMIF(INDIRECT("'"&listsheets&"'!C2:C358");$A$2;INDIRECT("'"&listsheets&"'!D2:D358")))



    "'"&listsheets&"'!C2:C358" - here is where I am looking for an item code

    $A$2 - here is the input of the item code

    "'"&listsheets&"'!D2:D358" - here is where the quantity (value) of the item should be returned


    It worked with the first few worksheets but when I add more (and there is a lot of them) formula starts to return 0's instead.

  • Hi,


    Have you already checked the way you have defined your named range : listsheets ...?


    Hope this will help

    ;)

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

  • Hi again,


    The formula you have used is indeed the right one ...

    But :

    1. Have you created your named range AFTER the creation of all your sheets ?

    2. Have you already saved your entire workbook once AFTER all these operations ?


    Hope this will help

    :)

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

  • Hi,


    1. Yes, the named range was named listsheets after all the sheets were created. I have created worksheet with INDEX list of all the worksheets. Because they still will be being added with time. So I wanted to track them there.

    2. Yes, I have saved the workbook after all these operations.


    But the formula still returns 0's,

    Another problem is whenever I re-open the file my listsheets is gone from the Name Manager. I have to do it over again. Could this be a problem?

  • Another problem is whenever I re-open the file my listsheets is gone from the Name Manager. I have to do it over again. Could this be a problem?


    Well ... my guess is that you have spotted the problem ...;)


    You should try to re-create your named range using the actual list in your Index worksheet ...

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