Forum Discussion

Ayudame1's avatar
Ayudame1
Copper Contributor
Sep 14, 2023

Excel Formula Help

I Need an excel formula to get total time spent in an account based on date, user, account number.   example below of my table data   time Account User Date 2:13 PM 123 A7 8/1/23 ...
excel
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 15, 2023

Ayudame1 

=VSTACK({"Account"."User"."Date"."Total time"},HSTACK(UNIQUE(B2:D9),DROP(REDUCE("",SEQUENCE(ROWS(UNIQUE(B2:D9))),LAMBDA(u,v,VSTACK(u,TAKE(FILTER(A2:A9,BYROW(B2:D9=INDEX(UNIQUE(B2:D9),v,),LAMBDA(x,AND(UNIQUE(TOCOL(x))=TRUE,COUNTA(UNIQUE(TOCOL(x)))=1)))),-1)-TAKE(FILTER(A2:A9,BYROW(B2:D9=INDEX(UNIQUE(B2:D9),v,),LAMBDA(x,AND(UNIQUE(TOCOL(x))=TRUE,COUNTA(UNIQUE(TOCOL(x)))=1)))),1)))),1)))

 

With Office 365 or Excel for the web you can apply this formula.

 

=INDEX($A$2:$A$9,LARGE(IF(($B$2:$B$9=B2)*($C$2:$C$9=C2)*($D$2:$D$9=D2),ROW($A$1:$A$8)),1))-INDEX($A$2:$A$9,SMALL(IF(($B$2:$B$9=B2)*($C$2:$C$9=C2)*($D$2:$D$9=D2),ROW($A$1:$A$8)),1))

 

An alternative for other versions of Excel could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. This formula is in cell E2 and copied down.