Forum Discussion

amoluf's avatar
amoluf
Copper Contributor
Jan 03, 2018

Problem using SumProduct() with Indirect() and Row()

I am using the formula    SUMPRODUCT(INDIRECT("B"&ROW(B7))=B3:B5)    which returns an error in Excel 2013 on Windows 10.  I have stripped my original formula to this, which gives an #ERROR! diagnostic.  If I replace the    ROW(B7)    with    7   the error goes away but gives the wrong answer. Am I doing something wrong? 

If I try    SUMPRODUCT(306={1116.9;306;1551.25})    the answer is 0, but should be 1 (because 306=306.)

This seems to work in LibreOffice and in Google Sheets.  

Any suggestions.

Formulas & Functions
Need Help
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 03, 2018
    As far as I can tell, this formula can be replaced with:
    SUMPRODUCT(B7=B3:B5)
    • amoluf's avatar
      amoluf
      Copper Contributor
      Jan 03, 2018

      As I mentioned, this is simplified from my original problem. I needed a formula using INDIRECT() with SUMPRODUCT() that had a row-relative address. It looks like Excel gives an error in this case. The version I posted is simple enough to suggest an Excel error handling ROW() inside INDIRECT() inside SUMPRODUCT().

    • amoluf's avatar
      amoluf
      Copper Contributor
      Jan 03, 2018

      To expand my response, in many years of programming computers I have learned at least two things: sometimes I can't see my own errors, and sometimes application software has bugs. I am new to TechCommunity, but I really appreciate that others will take the time to offer advice on my problems. Thank you all.

       

      Your comment that    SUMPRODUCT(B7=B3:B5)     could replace my #ERROR!-causing version overlooks my second issue. I had tried it, and like the version I reported using an inline array, your suggestion should work but gives 0 in Excel 2013 on Windows 10, when it should give 1.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jan 03, 2018

        Interesting.

        Within

        =SUMPRODUCT((INDIRECT("$B"&ROW(B7))=B3:B5)*1)

        INDIRECT transforms to the array

        INDIRECT({"$B7"})

        and returns #VALUE ! error within SUMPRODUCT. At the same time

        =SUMPRODUCT((INDIRECT("$B"&ROWS(B1:B7))=B3:B5)*1)

        works correctly. No array here, INDIRECT looks like

        INDIRECT("$B7")

        and returns correct result with SUMPRODUCT.

        Need to think bit more about that.

         

        As for the latest both

        =SUMPRODUCT(--(B7=B3:B5))   or
=SUMPRODUCT((B7=B3:B5)*1)

        will return correct result

Resources