Hello,
I have a long list of data that requires a TOTAL from various locations.... For each location, I need to calculate % by multiplying Productivity % * # of headcount for each then divide by overall total # of headcount . Currently, I use a formula where I have to manually enter each cell to get the total. I would like to use a more efficient formula so I thought of using SUMPRODUCT but I did not get the desired results. I thought this formula might help me avoid doing manual calculation which could result in error if incorrect cell is entered but unfortunately, I can't make it work. Perhaps I'm using incorrect formula or perhaps using SUMPRODUCT may not be the right solution.
I have attached the workbook to show my manual calculations (Row 8 from columns E to P). Below this row, highlighted in yellow, I attempted to use SUMPRODUCT hoping it will generate same results but it generated zero value.
Any advice is appreciated.
I have a long list of data that requires a TOTAL from various locations.... For each location, I need to calculate % by multiplying Productivity % * # of headcount for each then divide by overall total # of headcount . Currently, I use a formula where I have to manually enter each cell to get the total. I would like to use a more efficient formula so I thought of using SUMPRODUCT but I did not get the desired results. I thought this formula might help me avoid doing manual calculation which could result in error if incorrect cell is entered but unfortunately, I can't make it work. Perhaps I'm using incorrect formula or perhaps using SUMPRODUCT may not be the right solution.
I have attached the workbook to show my manual calculations (Row 8 from columns E to P). Below this row, highlighted in yellow, I attempted to use SUMPRODUCT hoping it will generate same results but it generated zero value.
Any advice is appreciated.