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