# SumProduct formula

Options
✭✭✭✭✭

hi,

i have task in smartsheet and each task i give factor which amounts 100%.

and each task i give actual % complate.

the problam is when i try to calculate the Weighted average i need to multiply each task in his factor.

if i delete or adding 10 to 50 tasks its Becomes exhausting

in excel its easy by using formula SUMPRODUCT.

any idea to resolve ?

• ✭✭✭✭✭✭
Options

I would suggest a helper column that could later be hidden. In this column I would use a formula such as

=Progress@row * Factor@row

Then you can sum this new column and multiply by 1.25.

=SUM([Helper Column]:[Helper Column]) * 1.25

• ✭✭✭✭✭
Options

hi,

i'm try to using a formula AVGW

i think its make the same effect as sumproduct in EXCEL

• ✭✭✭✭✭✭
Options

Using my previously mentioned method would duplicate the results of your originally posted formula, but it would allow you to add new rows to the calculation without having to manually add in additional rows to your end result. It is basically breaking it down into smaller pieces that function independently so that it is more automated.

I also see that you are using hierarchy, so you could use the CHILDREN function to roll up the results without having to manually specify row numbers.

• ✭✭✭✭✭
Options

How can i use in CHILDREN function in this case ?

• ✭✭✭✭✭✭
Options

If all the rows you are trying to sum are indented below the row you are entering the formula in, you would use something along the lines of

=SUM(CHILDREN())

This will sum all of the rows that are indented regardless of whether there are 5 or 50 rows. Then when you add a new "Child" row (another row indented below the formula row), it will automatically account for the new row.

• ✭✭✭✭✭
Options

good idea!

thanks you :)

• ✭✭✭✭✭✭
Options

Happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!