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!