CODEWITHSUNDEEP
arraysfilterhstack
Matt Procaccini
Matt Procaccini

Reputation: 1

Spill Array with filter and sum

Thanks in advance!!!

I have a data table (Transaction_Detail) of employee expenses from which I'd like to generate a summary result using the newer array functions.

I'd like to filter the results based upon:

a) date range (cell K$6 and L$6) and; b) expense type

and produce a two column summary of the Amount column by employee_name.

I've successfully gotten the results anticipated BUT I can only get the results with two separate excel array expressions. There has GOT to be a way to get this done in ONE expression - but I just cannot make it work! Help.

Expression 1 (first result array):

=LET(include?,--ISNUMBER(MATCH(Transaction_Detail[Account],            (FILTER(I9#,ISNUMBER(SEARCH("x",H9:H27)))),0))(Transaction_Detail[Date]>=K$6)(Transaction_Detail[Date]<=L$6),names,FILTER(Transaction_Detail[Simpler Name],include?),amounts,FILTER(Transaction_Detail[Amount],include?),HSTACK(names,amounts))

Expresson 2 (produces final result but references the output of Expression 1):

=SORT(HSTACK(UNIQUE(L45:L2048),SUMIFS(M45:M2048,L45:L2048,UNIQUE(L45:L2048))),2,-1)

Upvotes: 0

Views: 14

Answers (0)

Related Questions