Forum Discussion

A_SIRAT's avatar
A_SIRAT
Iron Contributor
Jun 14, 2022

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.

Thanks in advance.

Formulas and Functions
    • A_SIRAT's avatar
      A_SIRAT
      Iron Contributor
      Jun 16, 2022
      Thank you guys..

      I was avoiding Pivot Table though the other Solution from Peter is also complex.
  • 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