Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Xlookup with Sumproduct instead of Pivot table

Copper Contributor

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 the last 3 months.  This pulls through to graphs.   How do i do a xloopup combined with sumproduct?

 

SSMET75_0-1684307489101.png

 

 

 

6 Replies

@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?

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

@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.

 

@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))
            )
        )
    )
)
Sjo thanks
This is an extremely difficult formula. My staff will definitely mess this up. Is there no easier formula.