How to Measure Customers with Multiple Systems Serviced % in Excel

How to Calculate Customers with Multiple Systems Serviced % in Excel

1. Build a Unique Customer List

• Start by creating a unique list of Customer IDs from your dataset.
• Sort the list alphabetically to make the layout easier to manage.
• Add extra rows below the list so dynamic array results have room to spill when needed.
• Center the layout if you want the table to be easier to read while building it.

2. Create the Monthly Columns

• Use the service date field and convert each date to the end of its month with the EOMONTH function.
• Make sure the month offset is set correctly so each service date stays in its actual month.
• Build a unique list of those month-end dates.
• Use TRANSPOSE to place the monthly dates across the top row instead of down a column.
• Format the transposed values as dates.

3. Filter for Matching Customer, Month, and Service Type

• Create a dynamic array that checks each customer against each month.
• Use the FILTER function to return only the records that match:
  • the selected customer
  • the selected month
  • a job type of service
• Use system ID rather than system type so different units are treated correctly even when they share the same broad category.
• As you copy the logic across and down, lock the appropriate row or column references so the grid fills correctly.

4. Count Unique Systems for Each Customer-Month

• Wrap the filtered result in the UNIQUE function so duplicate systems are removed.
• Then use COUNTA to count how many unique systems remain for that customer in that month.
• Some cells may return no result, while others may spill because more than one system was found.
• The key is identifying where the count reaches two or more unique systems in the same month.

5. Convert the Count into a Yes/No Indicator

• Use an IF statement to turn the system count into a simple flag:
  • 1 if the customer had two or more unique systems serviced in that month
  • 0 if they did not
• Fill this logic across the monthly columns and down the customer list.
• This creates the customer-by-month matrix that identifies qualifying cases.

6. Total the Customers Who Qualify Each Month

• Add a summary row at the top or bottom of the matrix.
• Use SUM to total the number of customers flagged with a 1 in each month.
• This gives you the monthly count of customers with multiple systems serviced.

7. Calculate the Total Unique Service Customers by Month

• Build a second monthly summary that counts all unique customers who had a service job in each month.
• Use FILTER to return customer IDs for the selected month and service type.
• Wrap that result in UNIQUE so each customer is only counted once.
• Then use COUNTA to return the total number of unique service customers for that month.
• Copy this logic across the full date range.

8. Bring the Monthly Results into a Chart-Ready Table

• Create a smaller summary table that will feed the chart.
• Use TRANSPOSE again to bring the month labels into the chart section.
• Then use XLOOKUP to pull in:
  • the monthly count of customers with multiple systems serviced
  • the monthly total of unique service customers
• Lock the lookup ranges correctly so the formula works when dragged across.
• Reuse the same lookup structure for the second row instead of rebuilding it from scratch.

9. Calculate the Monthly Percentage and Add a Target

• Divide the monthly count of customers with multiple systems serviced by the monthly count of unique service customers.
• Format the result as a percentage.
• Add a target row above or below the KPI and repeat the same target value across all months.
• Because the target is linked to a cell, you can change it anytime and the chart will update automatically.

10. Create and Format the Chart

• Highlight the monthly date range, the calculated percentage row, and the target row.
• Insert a recommended chart and choose the visual that best fits the layout.
• Update the chart title to match the KPI.
• Adjust the percentage formatting so the axis and labels are easier to read.
• Make the target line thinner if you want the actual KPI trend to stand out more clearly.
• Add data labels only where they improve readability, especially on specific points instead of every value.

11. Final Cleanup

• Remove unnecessary decimal places from the percentages.
• Adjust label placement if the chart feels too busy.
• Use selective data labels or label backgrounds when you want to emphasize only a few months.
• Once the setup is finished, the monthly KPI updates automatically as the data changes.