CODEWITHSUNDEEP
arraysgoogle-sheetssumvlookuparray-formulas
gbarnabic
gbarnabic

Reputation: 187

Using an Arrayformula to sum another sheets values with a filter

I'm trying to use an array formula to sum up values in another sheet but filter the rows used in based on a match to a value. I can get the results with 2 different formulas without using array formulas but since the rows will be added automatically externally to the Sheet I can't just copy the formulas as each row is added. The formulas in both columns D and E work fine but my attempts at an arrayformula in both B1 and C1 are not working.

B1 = ={"Paid AF";ARRAYFORMULA(IF(LEN(A2:A),SUMIFS(Expenses!$B$2:$B,Expenses!$A$2:$A,A2:A),""))}

C1 = ={"Paid2";ARRAYFORMULA(IF(LEN(A2:A),INDEX(QUERY(FILTER(Expenses!$A$2:$B,Expenses!$A$2:$A = A2:A),"select SUM(Col2) ",0),2,1),""))}

enter image description here

https://docs.google.com/spreadsheets/d/13s2fHz6oFoNjipxqdqM-pCNrHAfLLwnq1G3iyxcVlb4/edit?usp=sharing

Upvotes: 1

Views: 456

Answers (1)

player0
player0

Reputation: 1

use:

={"Paid AF"; ARRAYFORMULA(IFNA(VLOOKUP(A2:A, QUERY({Expenses!A2:B}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1"), 2, )))}

enter image description here

Upvotes: 1

Related Questions