Converting =SUMPRODUCT from Excel to Smartsheet
How would I convert this formula to work in Smartsheet?
=SUMPRODUCT(($B$93:$B$137=$A144)*(J$93:J$137)*(1($G$93:$G$137)))
B93B137 = Project Type
J93J137 = [July thru 7/31]$125
I can get the first part using a SUMIF formula: =SUMIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [July thru 7/31]$83:[July thru 7/31]$125)
The last Part *(1($G$93:$G$137) is what stumps me. I need it to calculate the difference of a percentage listed in the cell.
Not sure if this can be done in one formula, or if it will require a helper sheet.
Answers

Are you able to provide screenshots of the source data?


What about the source data in Smartsheet?

It's essentially the same. It's the last Part (in green font in picture above) *(1($G$93:$G$137) is what stumps me. I need it to calculate the difference of a percentage listed in the cell. That column is named "Expected Revisions"
So far my formula is =SUMIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [July thru 7/31]$83:[July thru 7/31]$125)

Hi @Katrina
I agree that it may be helpful to see your data in Smartsheet to understand the columns better, however it sounds like you just need to get the Average from the Percentage column and then subtract that from 100% to find the remainder, is that correct?
* (1  AVG([Expected Revisions]$83:[Expected Revisions]$125))
If you need to AVG but only if the Project Type is the same, you could use the AVERAGEIF Function:
* (1  AVERAGEIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [Expected Revisions]$83:[Expected Revisions]$125))
Is that what you were looking to do?
Cheers,
Genevieve

Here is a sample of the Sheet. I've added in your formula, but still get an error message. ??

It looks like you have a misplaced parenthesis. Try moving one from the end to after the July column reference (before the comma).

Thank you!

Happy to help. 👍️

When I compared this formula to the "answer" in Excel, they are different. What the excel formula is doing is taking the difference the percent and multiplying it against the amount in the July column and then summing that for the total. It needs to do the calculation on the row before adding all the rows together, if that makes sense?
When I use this formula: =SUMIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [July thru 7/31]$83:[July thru 7/31]$125) + (1  SUMIF([Project Type]$83:[Project Type]$125, =$[Project Type]@row, [Expected Revisions]$83:[Expected Revisions]$125)) the total is 86.4, but the total should be 57.4. I can't figure out the correct syntax for the formula.

Hey @Katrina
Thanks for walking us through the math that you want it to do  Smartsheet formulas are different than Excel formulas as you've found, and I personally haven't used a SUMPRODUCT in Excel before so I don't know the intended outcome.
Based on what you're describing, having a helper column that calculates your values per row exactly like you're doing in the screen capture is what I would suggest! Then you can have this be set as a column formula so it automatically applies to all rows, and you can then use this helper column for any further calculations or summaries.
Cheers,
Genevieve

Thanks @Genevieve P. I haven't used SUMPRODUCT either, and just figured out how it worked myself. The problem with the helper column is that I will need 12 of them...one for each month.😵
Help Article Resources
Categories
Check out the Formula Handbook template!