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

[amoluf]

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.

[#2]

As far as I can tell, this formula can be replaced with:
SUMPRODUCT(B7=B3:B5)

[#3]

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().

[#4]

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.

[#5]

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

[#6]

Sergei, thank you so much for your reply. It looks like you have suggested good workarounds for both my issues.

But it seems to me that I need to submit bug reports on both of these issues to Microsoft, because the workarounds should not be necessary.

[#7]

Amoluf, the second one is definitely not a bug. Using

=SUMPRODUCT(B7=B3:B5)

you first receive the array like

{FALSE, TRUE, FALSE}

which Excel SUMs then. But SUM ignores logical and text values, thus you always have a zero as a result. To calculate the result you first have to transform logical values into numeric one, here we doing that with double-minus or multiplying on 1. With that the array first converted into

{0,1,0}

sum of which returns expected 1.

In brief, that's by design, how the Excel works.

As for the ROW() as array in SUMPRODUCT right now found this https://stackoverflow.com/questions/11124173/row-function-behaves-differently-inside-sum-and-sumprod... discussion which gives some explanation. It looks like also by design. When have bit more time will try to dig more.