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
HansVogelaar's avatar
HansVogelaar
MVP
May 17, 2023

SSMET75 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

SSMET75's avatar
SSMET75
Copper Contributor
May 17, 2023

HansVogelaar  Please find attached as requested.

 

 https://drive.google.com/drive/folders/1g6qO7liykLLz5up7GHVTRRTe1MhX19YN?usp=sharing 

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

Resources