Forum Discussion

olivermuk's avatar
olivermuk
Copper Contributor
Feb 28, 2025
Solved

Check 3 cells for values, then get value of 4th

Hi all   I'm struggling with this one. I have a table with a list of billable items. This data is a client name, product description (which includes a mixed string, but always contains an SKU), a p...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Feb 28, 2025

    Your data needs a bit of clean up (and I didn't presume you'd always have first of the month dates) and then PIVOTBY can handle it:

    =LET(
    dates, EOMONTH(+DemoTbl[Start Date], -1) + 1,
    product, REGEXEXTRACT(DemoTbl[Product], "(SKU\d+)"),
    row_labels, HSTACK(DemoTbl[Client name], product),
    values, DemoTbl[Value],
    PIVOTBY(row_labels, dates, values, SUM)
)

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 28, 2025

Your data needs a bit of clean up (and I didn't presume you'd always have first of the month dates) and then PIVOTBY can handle it:

=LET(
    dates, EOMONTH(+DemoTbl[Start Date], -1) + 1,
    product, REGEXEXTRACT(DemoTbl[Product], "(SKU\d+)"),
    row_labels, HSTACK(DemoTbl[Client name], product),
    values, DemoTbl[Value],
    PIVOTBY(row_labels, dates, values, SUM)
)

 

Resources