SumProduct formula

Netanel yosef

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 ?

Paul Newcome

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

Netanel yosef

hi,

i'm try to using a formula AVGW

i think its make the same effect as sumproduct in EXCEL

Paul Newcome

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.

Netanel yosef

How can i use in CHILDREN function in this case ?

Paul Newcome

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.

Netanel yosef

good idea!

thanks you :)

Paul Newcome

Happy to help! 👍️