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

Question

Hi all&nbsp;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 period start date, and a value for that period. There may be multiple lines for the same client, with the same SKU, for the same period with different values (in case of a refund being issued).&nbsp;I want to create a table within one line per SKU per client. If a client buys two SKUs from us, then it will have two lines for that client, and I'll enter one SKU on each line. I then need a formula to search the first table, find rows where "A:A = A24" AND where the same row in B:B contains B24 AND where the start date in C:C of the same row = the date of the column header in C23.&nbsp;&nbsp;Does that make sense? The upshot being that the green cells below show the total of that given clients spend on that specified SKU for that period.&nbsp;&nbsp;In the screenshot above I will fill out the Client name in A24 and the Product in B24, but I need a formula to find those two in the table at the top, and add up the totals for the period in the green part of the second table.&nbsp;&nbsp;Can anyone help?&nbsp;OIly

patrick2788 · Accepted Answer

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)
)&nbsp;

Forum Discussion

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

Share

Resources