Home > Enterprise >  Using an Arrayformula to sum another sheets values with a filter
Using an Arrayformula to sum another sheets values with a filter

Time:08-29

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

enter image description here

  • Related