Forum Discussion

Ad2Mollie's avatar
Ad2Mollie
Copper Contributor
Feb 19, 2022
Solved

Get total sales per customer

I have an excel spreadsheet of sales for the last couple of years. Is there a way to merge the individual customer's sales to remove the duplicate listing of the customer but retain the total sales f...
excel
Formulas and Functions
  • Teresa1810's avatar
    Teresa1810
    Mar 25, 2024

    Ad2Mollie 

    You probably already have an answer but I found a good solution and thought it might help others.

    Use the Consolidate feature on the data tab. 

    Good video here https://www.youtube.com/watch?v=IGaq_biQ1B8

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Mar 25, 2024

Ad2Mollie 

This is a simple exploration of what is possible with the latest (insider beta) version of Excel.

= LET(
    combined, VSTACK(PreventionTbl, EAPTbl, RecoveryTbl, DigitalTbl),
    GROUPBY(TAKE(combined,,1), DROP(combined,,1), SUM, 0, 1)
  )

Without GROUPBY life gets a little harder

= LET(
    combined,     VSTACK(PreventionTbl, EAPTbl, RecoveryTbl, DigitalTbl),
    corporations, TAKE(combined,,1),
    distinctCorp, UNIQUE(corporations),
    amounts,      DROP(combined,,1),
    item, MAKEARRAY(COUNTA(distinctCorp), COUNTA(header) - 1,
      LAMBDA(r,c,
        LET(
          yrAmount, CHOOSECOLS(amounts,c),
          distinct, INDEX(distinctCorp, r),
          SUM(FILTER(yrAmount, corporations=distinct)))
        )
      ),
    SORT(HSTACK(distinctCorp, item))
  )

 

Resources