LMNT Wholesale Customers Report - Ready-to-Use Formulas
Sheet Name: Wholesale Customers Data - Daily Updates RAW
Report Sheet: Report - All Wholesale Customers
Copy these formulas directly into your report sheet, starting in row 2.
Column A: Company Name
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!E2:E="", "", 'Wholesale Customers Data - Daily Updates RAW'!E2:E))Column B: Contact Name
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!D2:D="", "", 'Wholesale Customers Data - Daily Updates RAW'!D2:D))Column C: Email (with hyperlink)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!B2:B="", "", HYPERLINK("mailto:" & 'Wholesale Customers Data - Daily Updates RAW'!B2:B, 'Wholesale Customers Data - Daily Updates RAW'!B2:B)))Column D: Phone
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!C2:C="", "", 'Wholesale Customers Data - Daily Updates RAW'!C2:C))Column E: Customer ID
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!A2:A="", "", 'Wholesale Customers Data - Daily Updates RAW'!A2:A))Column F: Address Line 1
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!G2:G="", "", 'Wholesale Customers Data - Daily Updates RAW'!G2:G))Column G: Address Line 2
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!H2:H="", "", 'Wholesale Customers Data - Daily Updates RAW'!H2:H))Column H: City
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!I2:I="", "", 'Wholesale Customers Data - Daily Updates RAW'!I2:I))Column I: State
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!J2:J="", "", 'Wholesale Customers Data - Daily Updates RAW'!J2:J))Column J: Segment
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!F2:F="", "", 'Wholesale Customers Data - Daily Updates RAW'!F2:F))Column K: Business Type
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AD2:AD="", "", 'Wholesale Customers Data - Daily Updates RAW'!AD2:AD))Column L: Status
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!W2:W="", "", 'Wholesale Customers Data - Daily Updates RAW'!W2:W))Column M: Total Orders (Number format)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!O2:O="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!O2:O)))Column N: Total Revenue (Currency format - apply $#,##0.00)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!P2:P="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!P2:P)))Column O: Avg Order Value (Currency format - apply $#,##0.00)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!R2:R="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!R2:R)))Column P: Total Tax (Currency format - apply $#,##0.00)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!Q2:Q="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!Q2:Q)))Column Q: Last Order ID
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!N2:N="", "", 'Wholesale Customers Data - Daily Updates RAW'!N2:N))Column R: First Order Date (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!S2:S="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!S2:S),
TEXT('Wholesale Customers Data - Daily Updates RAW'!S2:S, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!S2:S),
TEXT('Wholesale Customers Data - Daily Updates RAW'!S2:S, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!S2:S, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!S2:S), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!S2:S)))))Column S: Last Order Date (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!T2:T="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!T2:T),
TEXT('Wholesale Customers Data - Daily Updates RAW'!T2:T, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!T2:T),
TEXT('Wholesale Customers Data - Daily Updates RAW'!T2:T, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!T2:T, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!T2:T), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!T2:T)))))Column T: First Wholesale Order (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!U2:U="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!U2:U),
TEXT('Wholesale Customers Data - Daily Updates RAW'!U2:U, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!U2:U),
TEXT('Wholesale Customers Data - Daily Updates RAW'!U2:U, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!U2:U, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!U2:U), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!U2:U)))))Column U: Days Since Last Order (Number format - apply #,##0)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!V2:V="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!V2:V)))Column V: Lifetime Days (Creation) (Number format - apply #,##0)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!X2:X="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!X2:X)))Column W: Lifetime Days (First Order) (Number format - apply #,##0)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!Y2:Y="", "", VALUE('Wholesale Customers Data - Daily Updates RAW'!Y2:Y)))Column X: Customer Created (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!Z2:Z="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!Z2:Z),
TEXT('Wholesale Customers Data - Daily Updates RAW'!Z2:Z, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!Z2:Z),
TEXT('Wholesale Customers Data - Daily Updates RAW'!Z2:Z, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!Z2:Z, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!Z2:Z), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!Z2:Z)))))Column Y: Email Consent
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!K2:K="", "", 'Wholesale Customers Data - Daily Updates RAW'!K2:K))Column Z: SMS Consent
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!L2:L="", "", 'Wholesale Customers Data - Daily Updates RAW'!L2:L))Column AA: Has Refrigerator (Convert TRUE/FALSE to Yes/No)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!M2:M="", "",
IF('Wholesale Customers Data - Daily Updates RAW'!M2:M=TRUE, "Yes",
IF('Wholesale Customers Data - Daily Updates RAW'!M2:M=FALSE, "No",
IF(UPPER('Wholesale Customers Data - Daily Updates RAW'!M2:M)="TRUE", "Yes",
IF(UPPER('Wholesale Customers Data - Daily Updates RAW'!M2:M)="FALSE", "No", 'Wholesale Customers Data - Daily Updates RAW'!M2:M))))))Column AB: Application Status
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AC2:AC="", "", 'Wholesale Customers Data - Daily Updates RAW'!AC2:AC))Column AC: Application Submitted (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AI2:AI="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!AI2:AI),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AI2:AI, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!AI2:AI),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AI2:AI, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!AI2:AI, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!AI2:AI), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!AI2:AI)))))Column AD: Onboarded Date (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!AJ2:AJ)))))Column AE: Has Physical Store
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AE2:AE="", "", 'Wholesale Customers Data - Daily Updates RAW'!AE2:AE))Column AF: Number of Locations
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AF2:AF="", "", 'Wholesale Customers Data - Daily Updates RAW'!AF2:AF))Column AG: Website (with hyperlink if URL)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AG2:AG="", "",
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!AG2:AG, "^(https?://|www\.)"),
HYPERLINK(IF(LEFT('Wholesale Customers Data - Daily Updates RAW'!AG2:AG, 4)="www.", "http://" & 'Wholesale Customers Data - Daily Updates RAW'!AG2:AG, 'Wholesale Customers Data - Daily Updates RAW'!AG2:AG), 'Wholesale Customers Data - Daily Updates RAW'!AG2:AG),
'Wholesale Customers Data - Daily Updates RAW'!AG2:AG)))Column AH: Social Handles
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AH2:AH="", "", 'Wholesale Customers Data - Daily Updates RAW'!AH2:AH))Column AI: Agreement Sent (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AK2:AK="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!AK2:AK),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AK2:AK, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!AK2:AK),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AK2:AK, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!AK2:AK, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!AK2:AK), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!AK2:AK)))))Column AJ: Agreement Signed (Date format - apply MM/DD/YYYY)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AL2:AL="", "",
IF(ISNUMBER('Wholesale Customers Data - Daily Updates RAW'!AL2:AL),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AL2:AL, "MM/DD/YYYY"),
IF(ISDATE('Wholesale Customers Data - Daily Updates RAW'!AL2:AL),
TEXT('Wholesale Customers Data - Daily Updates RAW'!AL2:AL, "MM/DD/YYYY"),
IF(REGEXMATCH('Wholesale Customers Data - Daily Updates RAW'!AL2:AL, "\d{4}-\d{2}-\d{2}"),
TEXT(DATEVALUE('Wholesale Customers Data - Daily Updates RAW'!AL2:AL), "MM/DD/YYYY"),
'Wholesale Customers Data - Daily Updates RAW'!AL2:AL)))))Column AK: Pricing Activated (Convert TRUE/FALSE to Yes/No)
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AM2:AM="", "",
IF('Wholesale Customers Data - Daily Updates RAW'!AM2:AM=TRUE, "Yes",
IF('Wholesale Customers Data - Daily Updates RAW'!AM2:AM=FALSE, "No",
IF(UPPER('Wholesale Customers Data - Daily Updates RAW'!AM2:AM)="TRUE", "Yes",
IF(UPPER('Wholesale Customers Data - Daily Updates RAW'!AM2:AM)="FALSE", "No", 'Wholesale Customers Data - Daily Updates RAW'!AM2:AM))))))Column AL: Discovery Source
=ARRAYFORMULA(IF('Wholesale Customers Data - Daily Updates RAW'!AP2:AP="", "", 'Wholesale Customers Data - Daily Updates RAW'!AP2:AP))Quick Setup Instructions
-
Create the report sheet:
- Create a new sheet named:
Report - All Wholesale Customers
- Create a new sheet named:
-
Add headers in row 1:
- Copy the headers from the specification document or use:
Company Name | Contact Name | Email | Phone | Customer ID | Address Line 1 | Address Line 2 | City | State | Segment | Business Type | Status | Total Orders | Total Revenue | Avg Order Value | Total Tax | Last Order ID | First Order Date | Last Order Date | First Wholesale Order | Days Since Last Order | Lifetime Days (Creation) | Lifetime Days (First Order) | Customer Created | Email Consent | SMS Consent | Has Refrigerator | Application Status | Application Submitted | Onboarded Date | Has Physical Store | Number of Locations | Website | Social Handles | Agreement Sent | Agreement Signed | Pricing Activated | Discovery Source -
Paste formulas starting in row 2:
- Column A2: Paste the Company Name formula
- Column B2: Paste the Contact Name formula
- Continue through Column AL2 with each formula
-
Apply number formatting:
- Currency columns (N, O, P): Select columns → Format → Number → Custom number format →
$#,##0.00 - Date columns (R, S, T, X, AC, AD, AI, AJ): Select columns → Format → Number → Date →
MM/DD/YYYYor Custom →MM/DD/YYYY - Integer columns (M, U, V, W): Select columns → Format → Number → Custom number format →
#,##0
- Currency columns (N, O, P): Select columns → Format → Number → Custom number format →
-
Apply conditional formatting:
- Status column (L): Format → Conditional formatting
- Text contains “Active” → Green background
- Text contains “Churned” → Red background
- Days Since Last Order (U): Format → Conditional formatting
- Less than 90 → Green
- 90-180 → Yellow
- Greater than 180 → Red
- Status column (L): Format → Conditional formatting
-
Freeze header row:
- Select row 2 → View → Freeze → 1 row
-
Add filters:
- Select row 1 → Data → Create a filter
Notes
- All formulas use ARRAYFORMULA so they automatically populate for all rows
- Formulas handle empty cells gracefully
- Date formulas handle multiple date formats (numeric dates, text dates, ISO dates)
- Boolean values are converted to Yes/No for readability
- Email and Website columns include hyperlinks where applicable