Is there a Way to Emulate SUMPRODUCT In Smartsheet With a Cross Sheet Reference?

Muhammad

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!

Scott Orsey

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!

Scott Orsey

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!