I’m having trouble figuring out how to create an ArrayFormula that can handle multiple conditions for summing data. I have a spreadsheet where I need to calculate totals based on matching criteria from different columns.
Basically, I have transaction records with ID numbers. Each payment entry has a destination account field and also includes the transaction ID so I can match payments to their original transactions. Some transactions get paid in multiple installments.
What I want to do is create a single array formula that will automatically sum all related payments for each transaction ID and account type. I can make this work with regular SUMIFS formulas, but I want to use one ArrayFormula in the top cell that fills down the entire column automatically.
Any ideas on how to structure this formula? Thanks for any help you can provide.
try QUERY instead of SUMIFS for this. something like =ArrayFormula(QUERY(data_range, "select col1, sum(col3) where col1 is not null group by col1")) handles multiple criteria way better. I’ve used it for similar stuff and its much cleaner than forcing SUMIFS into an array formula.
Had this same problem with customer payment data. Use SUMPRODUCT inside ArrayFormula - it handles multiple criteria way better than nested SUMIFS. Here’s what worked for me: =ArrayFormula(IF(A2:A<>"", SUMPRODUCT((transaction_ids=A2:A)*(account_types=B2:B)*payment_amounts), "")). Set up your ranges as named ranges first. The IF part stops errors on empty rows, and each row gets processed separately. Way more reliable than QUERY when you’ve got mixed data types, especially transaction IDs with text prefixes.
ArrayFormula with SUMIFS is tricky but totally doable. You need to wrap your SUMIFS properly: =ArrayFormula(SUMIFS(payment_range, transaction_id_range, A:A, account_type_range, B:B)) should work if your criteria columns are set up right. I hit the same issue with invoice tracking sheets. Main thing I learned - make sure your ranges are structured as arrays and you’re referencing the right columns for criteria. Also, blank rows in your data will create zeros everywhere in your results unless you add IF conditions to handle them. Test it on a small range first before running it on everything.