CODEWITHSUNDEEP
stringexcelif-statementsplitsum
potay
potay

Reputation: 23

excel sum all cells where another cell contains a string

Example Table:

  | A  |      B       
--------------------------
1 | 26 | tom, jerry 
--------------------------
2 | 12 | tom
--------------------------
3 |  6 | jerry, tom, dick

Suppose I have this table. What I am trying to do is to sum up the total of cells in column A where the cell of the same row in column B contains a certain name, for example "tom". However, before the cell in column A is added to the total, it has to be divided by the number of names in column B.

So for example, if I used the name jerry, I would get a total of:

(26/2) + (6/3) = 15

If I used the name tom, I would get a total of:

(26/2) + 12 + (6/3) = 27

Please help! I am thinking that perhaps it might be too complex and I might need to split it up.

Upvotes: 2

Views: 1637

Answers (2)

barry houdini
barry houdini

Reputation: 46331

List the names in D2 down and then in E2 put this formula and copy down

=IF(D2="","",SUMPRODUCT(A2:A10,ISNUMBER(SEARCH(D2,$B$2:$B$10))/(LEN($B$2:$B$10)-LEN(SUBSTITUTE($B$2:$B$10,",",""))+1)))

That assumes that all names in B2:B10 are separated by commas, so you can get a count of the names in each cell by adding 1 to the number of commas

Upvotes: 3

Peter Albert
Peter Albert

Reputation: 17475

Assuming that the name is in cell C1, this formula will do the job:

=SUM($A$1:$A$3*NOT(ISERROR(SEARCH(C1,$B$1:$B$3)))/(LEN($B$1:$B$3)-LEN(SUBSTITUTE($B$1:$B$3,",",""))+1))

You need to enter it as an array formula, i.e. press Ctrl-Shift-Enter.

Upvotes: 3

Related Questions