Xlookup with Sumproduct instead of Pivot table

Question

Good day&nbsp;I am trying to run xlookup and sum product instead of updating and changing source data of Pivot table each month.&nbsp;&nbsp;Each month, monthly data is added but the summary table must remain the last 3 months.&nbsp; This pulls through to graphs.&nbsp; &nbsp;How do i do a xloopup combined with sumproduct?&nbsp;&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

SSMET75&nbsp;
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 · Answer

HansVogelaar&nbsp; Please find attached as requested.&nbsp;&nbsp;https://drive.google.com/drive/folders/1g6qO7liykLLz5up7GHVTRRTe1MhX19YN?usp=sharing&nbsp;

patrick2788 · Answer

Are you essentially looking to pivot the data with a formula?

ssmet75 · Answer

Patrick2788&nbsp;&nbsp;I previously used a pivot table but get issues when I update or add data,&nbsp; 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.&nbsp; Sometime I need to go back a few months for comparison.&nbsp;So in essence yes.&nbsp; 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.&nbsp;

patrick2788 · Answer

SSMET75&nbsp;This would do it:&nbsp;=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))
            )
        )
    )
)

Forum Discussion

Xlookup with Sumproduct instead of Pivot table

6 Replies

Resources