CODEWITHSUNDEEP
google-sheetspivotgoogle-sheets-formulavlookupsubstitution
Atik Rahman
Atik Rahman

Reputation: 91

unique sum in google sheets

Hi I have sheets containing the data

enter image description here

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

enter image description here

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

Upvotes: 1

Views: 348

Answers (1)

player0
player0

Reputation: 1

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))

enter image description here

Upvotes: 1

Related Questions