Converting =SUMPRODUCT from Excel to Smartsheet

Katrina

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)))

B93-B137 = Project Type

J93-J137 = [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.

Paul Newcome

Are you able to provide screenshots of the source data?

Katrina

Paul Newcome

What about the source data in Smartsheet?

Katrina

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)

Genevieve P.

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

Katrina

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

Paul Newcome

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

Katrina

Thank you!

Paul Newcome

Happy to help. 👍️

Katrina

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.

Genevieve P.

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

Katrina

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.😵