Calculate percentage of each client separately

So I have this data with dates, clients and percentages that I'm trying to check.
The main goal is to get 100% for each client by combining the WBS elements.

In the example file: the clients in column I are AGA and IKI. First I need to check if every month equals 100% (yellow, orange and green indicate the example periods). I did so in column N. There could be mismatches, but in my example file both clients have 100%. But what if they don't?
Then I combined the client with WBS element in column O and found the unique values in column P.
In column Q I used the SUMIFS function to add up the percentages from column M for the unique client +WBS element.

But the problem is, that I would need to calculate the percentages of each client seperately, as I did in column V for AGA and IKI, just to show how it's supposed to look.
So I need to divide each percentage from the unique client + WBS element with the sum of the clients percentages and multiply by 100%.

I tried using this formula but to no avail.

Formula:

Please Login or Register  to view this content.

Maybe I could start from whole other end with this task.
But if not, then I would just need to get the same result as in column V, but with a formula.

Any ideas?

I use helper columns R & S, and in colum T, check for 100%.

In R3, copied down:

Formula:

Please Login or Register  to view this content.

In S3, copied down:

Formula:

Please Login or Register  to view this content.

In T3, copied down:

Formula:

Please Login or Register  to view this content.

Maybe there is a way to accomplish this with a one-go formula, but I have not yet mastered those new dynamic functions.
I hope this helps you.

Good luck!

Thank You for these formulas! Excel calculates much faster the 35k+ rows of data with these than with the ones I tried earlier.

But there is this one client ALS that has only one value in cell I2 (3,69) and the total is still 100.
Tho with all the other clients the total is 1200 and after calculating only the unique value I get 100 and so the total is always 100%.

I put one other client for the reference.

Am I not fully understanding the logic of it or is it some kind of an odd one out?

You're welcome. Glad to help.

Not sure I follow you, but this formula, for example in M2, copied down returns the sum per client. Maybe it will help you better visualize the calculation.

Formula:

Please Login or Register  to view this content.

With all the data it seems as if this formula:

Formula:

Please Login or Register  to view this content.

gives different values as this formula:

Formula:

Please Login or Register  to view this content.

How could I modify the second formula to show the values only where the last client ends/has the final value in column K?

The second formula returns blank unless there is a value in column L, which in turn will only show a value after the last client value from column K.

I totally did and tried to figure it out as well.... But couldn't manage with that

Of course, sorry

It does work perfectly, but I'd still have to create probably some helper columns to get the sum to 100, as it's not rounded to 2 decimal places. Or maybe it doesn't have to be. Maybe there's another way to solve it.
Tho I believe helper columns are the way for it.