Excel Formula Help

Occasional Visitor

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

@PTP3003 

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.

 

See How to Set Up Excel Pivot Table 

@PTP3003 

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

 

departments.JPG

@PTP3003 

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)