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.

         

Resources