yogi_Display Each Employee And Hours Worked From A Table Consisting Of Multiple Names And Their Hours

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #607   Jul 3, 2012     www.energyefficientbuild.com.


user benomatic42 said:
UNIQUE failure, plus conditional sums
Hi hi,

I am trying to work a fairly simple spreadsheet into simple, per-person counting.  I have data like this:

  https://docs.google.com/spreadsheet/ccc?key=0AvDqLN02sJwJdEc2bTZWS2FZVGJuSWVfSmhvR1dNaVE

Basically, 2 columns.  1st column is of comma separated strings, 2nd is a count.

[ Ben           | 3 ]

[ Ben, Tim      | 5 ]

[ Tim, Fred     | 2 ]

[ FRED, GEORGE  | 7 ]

What I seek is a summary of the count (hours), per unique person.  It could (but need not) look like:

[ ben    | 8 ]

[ fred   | 9 ]

[ george | 7 ]

[ tim    | 7 ]

My first function, to separate and uniquify the names, is my first failure:

  =UNIQUE(SPLIT(LOWER(JOIN(" ";F2:F20)); " ,;"))

The unique seems to do nothing. I also tried to use SORT on it, but that also failed to change the result. Suggestions?

Thanks,

ben

-------------------------------------------------------------------------------------------------------

following is a solution to the problem