Is there a Way to Emulate SUMPRODUCT In Smartsheet With a Cross Sheet Reference?
Hi all!
I am trying to create a SUMPRODUCT in Smartsheet, however when I do I receive the "INVALID OPERATION" error. This is how I was trying to go about it; I created a helper column where I would multiply both A x B, then I was simply going to sum up all the products. The issue I'm encountering is with the first part. I am trying to make cell formulas for each row (there are only 6 rows), and this requires a cross sheet reference between the current the cell at a current row, and a corresponding cell in a in another sheet. It is at this point that I receive the error. Here is the formula I am attempting to use:
=[SheetAValue]@row * IF({SheetB: ROW#} = [SheetAROW#]@row, {SheetBValue})
To explain the above formula it works like this: I have a helper column called ROW, where it keeps track of the row numbers. Here, I labeled them "SheetAROW" and "SheetBROW" just to avoid confusion. My thought with this was return the value on the same row using the IF from sheet B, then multiply it by the value in the corresponding cell in Sheet A. For some reason this returns the INVALID OPERATION error, and I don't understand why or how to fix it. Any and all help would be appreciated!
Best Answer

Hi... yeah. Sorry. IF() won't work on a range like that. Instead you'll want to use INDEX/MATCH. Try this:
=[SheetAValue]@row * INDEX({SheetBValue}:{SheetBValue},MATCH( [SheetAROW#]@row,{SheetB: ROW#},0))
Good luck!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Answers

Hi... yeah. Sorry. IF() won't work on a range like that. Instead you'll want to use INDEX/MATCH. Try this:
=[SheetAValue]@row * INDEX({SheetBValue}:{SheetBValue},MATCH( [SheetAROW#]@row,{SheetB: ROW#},0))
Good luck!
If my response was helpful or answered your question please be sure to upvote it, mark it asawesome, or mark it as the accepted answer!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!