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
 Smartsheet Customer Resources
 62.9K Get Help
 379 Global Discussions
 210 Industry Talk
 441 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 300 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!