Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Jun 14, 2022
Solved

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 ...
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 14, 2022

    Hi A_SIRAT 

     

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

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jun 14, 2022

A_SIRAT 

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

 

 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 14, 2022

    PeterBartholomew1 

    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.

Resources