• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT Formula - Is this appropriate solution to calculate total sum of several equations?

Josiev

New Member
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.
 

Attachments

  • SumProduct Formula.xlsx
    90.1 KB · Views: 9
A horrible formula dependent on the No. of licenced engineers row always being the same number of rows (4) below the Engineering Productivity row in all the sub-tables where one of those rows exists; in E8:
Code:
=SUMPRODUCT(($C$41:$C$645=$C$8)*(E$41:E$645),($C$45:$C$649=$C$11)*(E$45:E$649))/E$11
copied across.
 
Last edited:
Hi P45cal,

Thank you for the quick response....I see what you mean, now I understand where I made the error. I copied your formula and applied to my workbook, I now get the desired value. This truly is a big help not only for this particular issue I had but also how I need to apply this formula properly going forward, much appreciated.
 
Back
Top