yogi_Compute Hours Worked By Name And Project With Working Time Hours Not To Exceed Specified Amount

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #708   Aug 22, 2012     www.energyefficientbuild.com.

user SamWolf said: (http://productforums.google.com/forum/?zx=ahxxx0f1ye6h#!category-topic/docs/spreadsheets/I1h6t62iwkU)
ARRAYFORMULA with a filter
Hello All,

I have a very simple formula in my form and i would like to make it an arrayformula.

The formula is: =sum(filter(D:D,A:A=A3,B:B=B3,C:C=C3))

I tryed all kind of combenation and i cant make it work.

----

My project is kind of simple i have 2 spreadsheets:

spreadsheet 1:

https://docs.google.com/a/saia.co.il/spreadsheet/ccc?key=0At4Q9U2JTzfJdF9SLVZLVlFHWEdrdnkyYW02ZHgwalE#gid=0 

The sheet "2012" has a list of reports coming from a form where every worker types in from what time till what time he worked on which project (coded by numbers).

The other sheet "DoneData" is the same after "fixing" the dates (taking of the "time" from the time stamp).

spreadsheet 2: 

https://docs.google.com/a/saia.co.il/spreadsheet/ccc?key=0At4Q9U2JTzfJdHRHdUtQZFN5d2ljQWcwZHM2b3Y0QXc#gid=0 

the Sheet "Sub" has a list of projects.

What i am trying to do is to have in Column "D" sum of all the Horus i worked till now. BUT every day that i worked over 10 houres needs to be calcelated as 10 Horus - not more.

I'm really sorry if it to complicated - feel free to answer no.

Any ideas?

Best Regards.

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

following is a solution to the problem