SumProduct formula
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 ?
Answers

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

hi,
i'm try to using a formula AVGW
i think its make the same effect as sumproduct in EXCEL

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.

How can i use in CHILDREN function in this case ?

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.

good idea!
thanks you :)

Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!