Jun 14 2022 10:24 AM
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.
Thanks in advance.
Jun 14 2022 10:49 AM
SolutionHi @A_SIRAT
A Pivot Table does what you want automatically (recommend you format your data range as a Table). See attached file
Jun 14 2022 12:54 PM - edited Jun 14 2022 01:04 PM
This demonstrates Excel 365 as a programming environment
where the Lambda function is long but routine.
AddSubTotalsλ = LAMBDA(hdr, tbl,
LET(
distinctCat, UNIQUE(Category),
report, REDUCE(header, distinctCat,
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)
)
)
Jun 14 2022 01:29 PM
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),
report, REDUCE(header, distinctCat, StackGroupλ),
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.
Jun 16 2022 08:52 AM
Jun 14 2022 10:49 AM
SolutionHi @A_SIRAT
A Pivot Table does what you want automatically (recommend you format your data range as a Table). See attached file