Cost per Sale (CPS):
Track Your True Cost to Win Deals by Channel

Cost per Sale Setup in Excel

1. Confirm the required columns in your dataset

• Lead date
• Lead source or marketing channel
• Lead cost
• Sale closed status (Yes/No)
• Gross profit

2. Add Month and Year fields from the Lead Date

• Insert two new columns to the right of your dataset: Month and Year
• Use the TEXT function to extract the month name
  • =TEXT([Lead Date],”mmmm”)
• Use the TEXT function to extract the year
  • =TEXT([Lead Date],”yyyy”)
• Fill down for all rows
• If you already created Month and Year earlier, you can keep those columns and skip rebuilding them

3. Create a unique list of Months and Years for the heat map rows

• Build a unique list based on the Month field you created
• Use UNIQUE() on the Month column range
• Copy the resulting list and paste values using Ctrl + Shift + V
• Repeat the process for Year if you are listing years separately
• Place Month and Year so your heat map rows reference them (example shown in the video: Month in B3 and Year in B2 or nearby)

4. Create a unique, sorted, transposed list of Channels across the top

• Use UNIQUE() on the Marketing Channel field (table reference shown in the video)
• Sort A to Z using SORT()
• Transpose the sorted list across columns using TRANSPOSE()
• Copy the resulting header row and paste values using Ctrl + Shift + V
• You should now have channels across the top and Month and Year down the left

5. Calculate total spend by Month, Year, and Channel with SUMIFS

• In the first cell of the matrix, calculate total lead cost for that Month, Year, and Channel
• Use SUMIFS() where:
  • Sum range = Lead Cost column
  • Criteria ranges = Month column, Year column, Channel column
  • Criteria = the Month cell, Year cell, and Channel header cell
• This gives you total marketing spend for that channel in that month and year

6. Calculate the number of sales with COUNTIFS

• In the denominator, count how many rows match:
  • Month = selected Month
  • Year = selected Year
  • Channel = selected Channel
  • Sale Closed = Yes
• Use COUNTIFS() with those four criteria
• This produces the number of closed sales for that channel in that month and year

7. Compute CPS as Spend divided by Sales

• Divide the SUMIFS result by the COUNTIFS result
• Format the result as currency
• Drag across for all channels and down for all months

8. Fix copying issues by locking references with dollar signs

• Update your references so they behave correctly when you copy across and down:
  • Lock the Month and Year columns so they do not shift when copying across
  • Lock the Channel row so it does not shift when copying down
• Use dollar signs to lock:
  • Column lock: $B3 or $C3
  • Row lock: D$2
• Do not drag table formulas if Excel starts switching column names unexpectedly
• Copy the finished formula and paste into the full range instead

9. Remove channels with zero spend if needed

• If some channels show all zeros because there is no paid spend (example: Organic Search), delete those columns from the matrix
• Optionally keep referral channels if you track referral bonuses as spend

10. Handle divide-by-zero errors with IFERROR

• If spend exists but sales are zero, CPS will error due to dividing by zero
• Wrap the full CPS formula in IFERROR() and return a dash
  • Example output: –
• Copy and paste the updated formula across the full matrix

11. Add an overall CPS by channel

• Create a separate “overall” row that calculates CPS for the entire dataset by channel
• Reuse your CPS formula but remove the Month and Year criteria
• Leave only:
  • Spend summed by Channel
  • Sales counted by Channel with Sale Closed = Yes
• Copy across to get an overall CPS for each channel

12. Apply conditional formatting to flag expensive CPS

• Type your threshold in a cell (example used: 250)
• Select the full CPS matrix
• Home → Conditional Formatting → Highlight Cell Rules → Greater Than
• Set the rule to highlight values greater than your threshold
• This quickly shows which channels are producing sales at high cost

13. Add the profit context calculations shown at the end

• Gross Profit per Sale by channel
  • Sum gross profit by channel where Sale Closed = Yes
  • Divide by number of sales for that channel
• Dollars per sale by channel
  • Gross Profit per Sale minus CPS
• Gross Profit Return on Ad Spend by channel
  • Gross Profit per Sale divided by CPS

These final three outputs are built as simple combinations of SUMIFS and COUNTIFS results, then basic arithmetic on the outputs.