Using an Arrayformula to sum another sheets values with a filter

Question

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

player0 · Accepted Answer · 2022-08-28 17:33:24Z

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

answered Aug 28, 2022 at 17:33

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using an Arrayformula to sum another sheets values with a filter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related