Need the excel formula for following function

Question

Hi Geeks,

I need help to do the following function in a MS Excel sheet. The sheet example is as follows

       A              B         C          D              E 
1     TimeStamp       Name     Amount     UsedBy        Description
     -----------------------------------------------------------
2     Date1           Me1       200       He1,She1        desc1
3     Date2           Me1       100       Me1,He1         desc2
4     Date3          She1        50       He1,She1,Me1    desc3
5     Date4           He1        70       She1,He1        desc4
6     Date5          She1       200       She1,He1,Me1    desc5
7     Date6           Me1        22       He1             desc6

I want some function which can do the following sequence of job in a single customized MS-Excel formula

1. Sum the cells of column "Amount" where "UsedBy" column cells contain "He1" as a single entity. Lets say result is X
2. Sum of the cells of column "Amount" where "UsedBy" column cells contain two entities and "He1" must be one entity. After this sum devide it by 2. Lets say result is Y.
3. Sum of the cells of column "Amount" where "UsedBy" column cells contain three entities and "He1" must be one entity. After this sum devide it by 3. Lets say result is Z
4. Total the result in steps 1,2 and 3. That means the sum of X+Y+Z

Please let me know if I am not clear in my question....

Answer 1

Try the SUMIF function.

Answer 2

Build some intermediate results like the number of values in UsedBy, or whether UsedBy contains He1 in separate columns, then use SUMIF().