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),""))}
https://docs.google.com/spreadsheets/d/13s2fHz6oFoNjipxqdqM-pCNrHAfLLwnq1G3iyxcVlb4/edit?usp=sharing
>Solution :
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, )))}
