Please Add a Sum Product Formula

Samuel Mueller

Please add a sum product formula to improve ease of calculations without workarounds.

Paul Newcome

All of the examples I have seen regarding SUMPRODUCT can be replaced with a basic SUMIFS. Can you provide some example use cases where workarounds are needed as opposed to some functions that are already built in?

Samuel Mueller

@Paul Newcome Can you show me an example where a sumifs could be used in place? I know we can use helper columns to achieve the end result. Looking for capabilities that do not require workarounds. Something we could also incorporate a hierarchy function as well.

It's kind of hard to explain my situation, but effectively I need a sum prod formula, without using additional helper columns. I did use a workaround. But I feel like the purpose of enhancement requests is to reduce the need for workarounds.

Paul Newcome

I agree that the purpose of the enhancement request is to reduce the need for workarounds, but I didn't see where a workaround was needed until you presented your use case.

From Microsoft's Website:

A SUMIFS would generate the same result

=SUMIFS(D2:D7, B2:B7, @cell = B@row, C2:C7, @cell = C@row)

Two more examples from the same page:

First one would be SUMIFS + SUMIFS, and second one would be a straight SUMIFS again.

But it looks like in your example you are needing to use weights before summing the hours. I've not used the SUMPRODUCT in Excel before, so I was just going based on what I was seeing on Microsoft's page. Seeing your use case is definitely helpful.

Samuel Mueller

Fair enough. I appreciate your feedback! I've actually never seen a sumproduct used in those examples you shared so that was interesting to see. This example from that page is most in line with what I was trying to achieve in a single formula without helper columns.

Paul Newcome

And I had never really used SUMPRODUCT before, so I was only able to go off of what I was seeing there. Haha.

Either way... Your use case definitely currently requires a helper column in Smartsheet, so the SUMPRODUCT function would be useful. I just wasn't sure what you were getting at until I saw your use case.

Salem K

Helpful for EVM calculations