Jan 17 2023 11:25 AM
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.
Jan 17 2023 11:32 AM
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.
Jan 17 2023 11:40 AM
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))))
Jan 17 2023 12:37 PM
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)