Forum Discussion

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! diagnosti...

Formulas & Functions

Need Help

JKPieterse

Silver Contributor

Jan 03, 2018

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

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