Forum Discussion

SSMET75's avatar
SSMET75
Copper Contributor
May 17, 2023

Xlookup with Sumproduct instead of Pivot table

Good day   I am trying to run xlookup and sum product instead of updating and changing source data of Pivot table each month.   Each month, monthly data is added but the summary table must remain ...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
May 17, 2023
Are you essentially looking to pivot the data with a formula?
SSMET75's avatar
SSMET75
Copper Contributor
May 17, 2023

Patrick2788 

 

I previously used a pivot table but get issues when I update or add data,  the data is flowing through to graphs and other tables.

I want a more rebuts table, that is more flexible with xlookup.

I have 36 months of data and is adding as we grow.  Sometime I need to go back a few months for comparison.

 

So in essence yes.  I want to replace pivot tables with xlookup/sumif where I can just change the summary date and the rest of the data is looked up and sum automatically.

 

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 17, 2023

    SSMET75 

    This would do it:

     

    =LET(
    header, Table1[#Headers],
    months, TAKE(Table1, , -3),
    mgr, TAKE(Table1, , 1),
    REDUCE(
        header,
        SORT(UNIQUE(mgr)),
        LAMBDA(a, v,
            LET(
                filtered, FILTER(months, mgr = v),
                feb, SUM(TAKE(filtered, , 1)),
                mar, SUM(TAKE(DROP(filtered, , 1), , 1)),
                apr, SUM(TAKE(filtered, , -1)),
                VSTACK(a, HSTACK(v, feb, mar, apr))
            )
        )
    )
)
    • SSMET75's avatar
      SSMET75
      Copper Contributor
      May 19, 2023
      Sjo thanks
      This is an extremely difficult formula. My staff will definitely mess this up. Is there no easier formula.

Resources