Forum Discussion
Excel Formula Help
Hello! I need some help with formulas. I'm making an expense sheet and want to make a vertical column for "departments". Looking to create a formula that adds up the different departments in that single column with a line under the table for each department that displays the total.
3 Replies
Here's a solution using REDUCE:
Lambda Summarize(a, v) =LET(filtered, SUM(FILTER(val, dept = v)), VSTACK(a, HSTACK(v, filtered))) Sheet Level formula: =REDUCE({"Dept","Val"},UNIQUE(Dept),Summarize)With Office 365 or Excel 2021 or Excel for the web you can try this:
=HSTACK(SORT(UNIQUE(C1:C9)),BYROW(SORT(UNIQUE(C1:C9)),LAMBDA(row,SUMIF(C1:C9,row,D1:D9))))
I'd create a pivot table based on the data. Add the department field (column) to the Rows area and the expense field to the Values area.
