Forum Discussion
Conditional Sum
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.
taira727 this will work based on a couple of assumptions: a) the date on in column A is entered as a date, b) the user knows to only enter the date on the first line, and c) the user will enter a date after the last line. Note that any/all of these could be accommodated for but would take additional tweaks in the formula:
=IF(A6,SUM(INDEX(F:F,MATCH(MAXIFS(A:A,A:A,"<"&A6),A:A))),"")see attached
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
