Forum Discussion
Problem using SumProduct() with Indirect() and Row()
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