Hi I have sheets containing the data

What I want is total paid and pending amount of each client

I have tried ={unique(F2:F),ArrayFormula(sumif(F2:F5,unique(F2:F),E2:E))} seems like not working.

CodePudding user response：

try:

=INDEX(IFNA(VLOOKUP(I3:I, QUERY({E2:F, SUBSTITUTE(G2:G, "Pending", "Unpaid")}, 
 "select Col2,sum(Col1) where Col1 is not null group by Col2 pivot Col3"), {2, 3}, 0)*1))