SOLVED

Formula to filter then create Subtotals

Contributor

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.

4 Replies
best response confirmed by A_SIRAT (Contributor)
Solution

Hi @A_SIRAT 

 

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

@A_SIRAT 

This demonstrates Excel 365 as a programming environment

image.png

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

 

 

@Peter Bartholomew 

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.

Thank you guys..

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