Forum Discussion
Solved
Conditional Sum
Hello, I am trying to make timesheet for my team. Is there any good way to have calmative sum of each day with formula? I had some success with "sum if" function as shown in the first image. Howe...
taira727 Ooops my bad I missed a ref. Here is the corrected formula that gets copied down:
=IF(A4,SUM(INDEX(F:F,MATCH(MAXIFS(A:A,A:A,"<"&A4),A:A)):F3),"")I also created a dynamic array version (i.e. just paste in the top cell and it auto fills down)
=LET(d,$A$3:INDEX(A:A,COUNTA(B:B)+1),t,$F$3:INDEX(F:F,COUNTA(B:B)),s,SEQUENCE(ROWS(t),1,1),**bleep**,SCAN(0,s,LAMBDA(c,i,IF(INDEX(d,i),INDEX(t,i),c+INDEX(t,i)))),IF(INDEX(d,s+1)>0,**bleep**,""))see attachment.
Really appreciate your insight here. When I open the attached file, I see numbers under "day sum" column is not exactly sum of each day - any ideas how to make this work?
Thanks again!
taira727 Ooops my bad I missed a ref. Here is the corrected formula that gets copied down:
=IF(A4,SUM(INDEX(F:F,MATCH(MAXIFS(A:A,A:A,"<"&A4),A:A)):F3),"")I also created a dynamic array version (i.e. just paste in the top cell and it auto fills down)
=LET(d,$A$3:INDEX(A:A,COUNTA(B:B)+1),t,$F$3:INDEX(F:F,COUNTA(B:B)),s,SEQUENCE(ROWS(t),1,1),**bleep**,SCAN(0,s,LAMBDA(c,i,IF(INDEX(d,i),INDEX(t,i),c+INDEX(t,i)))),IF(INDEX(d,s+1)>0,**bleep**,""))see attachment.
- Awesome! Thank you so much