Multiply cell by amount of names in another cell with exclusions

Hi All

Hoping for some help here please.

I am extracting data from our scheduling tool and need to be able to multiply a value by the amount of people assigned to that task with certain exclusions.

The people assigned to the task are all listed in the same cell, separated by comma's and in between [ ].

Using row 2 from the attached worksheet as an example, the assignees in column D are [James Brown, James Dice] and the time estimate in column G is 1.30. As there are 2 names in cell D2, I need to multiply cell G2 by 2 in either G2 or in J2 (empty cell).


However there are 3 assignees "R", "S" and "T" that will appear in column D which I need to exclude from this. For example cell D3 is [Liam Heath, T] and should therefore only multiply by 1. Another example in cell D5 - [James Bond, James Dice, James Brown, R] - This should only multiply by 3.


Some cells in column D will only have [] which can be left alone / multiply by 0 / multiply by 1. Whichever is easiest to achieve given the above request.


In the example I have used made up names. R, S, and T are actual however and have not been changed.


Thank you

Hi AliGW

Yes R, S and T will appear that way.

The formula works perfectly except for when R, S and T are at the beginning of the list of names. Apologies this example was not included in the sample. I have reattached the sample with expected values and another example all highlighted in green, with your formula at the end.

That solves the issue however now actual names that end with the letter R, S or T followed by another name or R, S or T - (example [Marcel Cooper, S]) - now multiplies. This can be seen in row 7.

Thank you. For now I will use find and replace to remove entire surnames ending in R, S or T. Your formula works perfectly following this.

The 365 version can be simplified as much as possible. It is really powerful. Learn from Bo_Ry GURU. If I have the opportunity to upgrade and study the 365 version