excel - Conditional subtraction from multiple cells -

a    |b    |c    |....|k    |l    |m    | tom  |0    |     |....|tom  |jim  |dave | jim  |1000 |     |....|15000|14000|12000| dave |3000 |     |....|     |     |     | 

using google sheets one. values in columns k, l, , m read column b, detect if corresponding cell a reads 1 of 'tom', 'jim', or 'dave' example, , subtract amount correct column reduce running total. i've had trouble figuring out , tried use conditional formatting solve can't seem quite there. there formula can use read column b , subtract amount shown correct column based on name in column a?

so pseudo-code it:

read(column b cell); if(column b cell - 1 column = "tom") { column k - (value of column b cell) }  else if(column b cell - 1 column = "jim") { column l - (value of column b cell) } 

etc.

is there simple method can use generate result? thought changing formatting of cell based on name in cell next , subtracting value of cell colour becomes unwieldy if names added. assistance appreciated!

would there way constrain formula single cell can have total in single location rather down columns? plan have total boxes scroll sheet , visible.

let's assume columns a , b continue on down sheet, further name + amount entries. want have single row of balances each of people.

the balance initial value less sum of amounts person. amounts you've shown in row 2 initial values; here's how have row 3 reflect remaining balance each person (this "tom", copy others):

=k2-sumif($a$2:$b,"="&k$1,$b$2:$b) 

alternative solution

this doesn't want, more appropriate running total scenario, others may find useful adapt ledgers, etc.

the if() function can used decide whether or not value in column b applies 1 of "total" columns, k m.

use formula in k3, copy rest of range:

=k2-if($a2=k$1,$b2,0) 

this example in google sheets, same formula works in excel , other "compatible" offerings.