Forum Discussion
Problem using SumProduct() with Indirect() and Row()
SUMPRODUCT(B7=B3:B5)
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.
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
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.
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-sumproduct discussion which gives some explanation. It looks like also by design. When have bit more time will try to dig more.
