SOLVED

Iron Contributor

# Formula to filter then create Subtotals

Hi,

I am trying to create a report ( similar to a subtotal function summary) but without using the subtotal method.

Data is on the source data sheet. I need a formula or idea to filter the data then get a subtotal report from the source data based on category. I can enter all the items then do a SUMIFS but may be there is a smarter way to do this.

4 Replies
best response confirmed by A_SIRAT (Iron Contributor)
Solution

# Re: Formula to filter then create Subtotals

Hi @A_SIRAT

A Pivot Table does what you want automatically (recommend you format your data range as a Table). See attached file

# Re: Formula to filter then create Subtotals

This demonstrates Excel 365 as a programming environment

where the Lambda function is long but routine.

``````AddSubTotalsλ = LAMBDA(hdr, tbl,
LET(
distinctCat, UNIQUE(Category),
LAMBDA(output,dCat,
LET(
group,    FILTER(table,Category=dCat),
quantity, CHOOSECOLS(group,3),
boxes,    CHOOSECOLS(group,4),
subtotal, HSTACK("","Subtotal "&dCat,SUM(quantity),SUM(boxes)),
VSTACK(output,group,subtotal)
)
)
),
DROP(report,,1)
)
)``````

# Re: Formula to filter then create Subtotals

A slight change in style, namely breaking the Lambda functions down to be closer to bite sized:

``````AddSubTotalsλ =  lAMBDA(hdr, tbl,
LET(
distinctCat, UNIQUE(Category),
DROP(report,,1)
)
);

StackGroupλ = LAMBDA(output,dCat,
LET(
group,    FILTER(table,Category=dCat),
quantity, CHOOSECOLS(group,3),
boxes,    CHOOSECOLS(group,4),
subtotal, HSTACK("","Subtotal "&dCat,SUM(quantity),SUM(boxes)),
VSTACK(output,group,subtotal)
)
)``````

For readability, I think I prefer the Lambda functions used within the helper functions to be Named.  Feel free to disagree.

# Re: Formula to filter then create Subtotals

Thank you guys..

I was avoiding Pivot Table though the other Solution from Peter is also complex.
1 best response

Accepted Solutions
best response confirmed by A_SIRAT (Iron Contributor)
Solution

# Re: Formula to filter then create Subtotals

Hi @A_SIRAT

A Pivot Table does what you want automatically (recommend you format your data range as a Table). See attached file