Function Problems

How to Count Unique Patient Referrals by Test Category in Spreadsheets

Chanuka Geekiyanage Sep 30, 2026 869 views

Question details

The user needs to group data by patient ID and count the distinct testing categories to determine the exact number of unique referrals.

How to Count Unique Patient Referrals by Test Category

Product: Spreadsheets / Python
Device & OS: not provided
Scenario: Analyzing medical data where individual patients have multiple rows for different tests, making traditional row-counting inaccurate.
Observed behavior: Using a standard count function overstates the number of actual patient referrals because it counts duplicate patient rows.

Before you start

Ensure your dataset is organized with clear column headers, specifically for 'Patient ID' and 'Testing Category', and remove any completely blank rows before beginning the analysis.

Solution 1Recommended

Use a Data Model PivotTable for Distinct Counts (Excel/Spreadsheets)

This is the most efficient method in spreadsheet software to calculate distinct counts without writing complex formulas.

By adding your data to the Data Model when creating a PivotTable, you unlock the 'Distinct Count' summarization feature. This allows you to count unique testing categories per patient easily.

Select your data and insert a PivotTable

Highlight your entire dataset. Go to the 'Insert' tab on the ribbon and click 'PivotTable'.

Enable the Data Model

In the Create PivotTable dialog box, ensure you check the box that says 'Add this data to the Data Model'. Click OK.

Configure the PivotTable fields

In the PivotTable Fields pane, drag the 'Patient ID' field into the Rows area. Then, drag 'Testing Category' into the Values area.

Change to Distinct Count

Click the drop-down arrow next to 'Testing Category' in the Values area and select 'Value Field Settings'. Scroll down to the bottom of the list, choose 'Distinct Count', and click OK.

Use a Data Model PivotTable for Distinct Counts (Excel/Spreadsheets)

Data Accuracy: Your PivotTable will now accurately display the number of unique testing categories for each patient without double-counting the rows.

Solution 2

Use Python and Pandas for Large Datasets

Ideal for data analysts or users dealing with massive datasets where spreadsheet software might become slow.

Efficient Data Analysis

Count Unique Values Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful PivotTable features and advanced functions, allowing you to quickly group data and count distinct entries like patient referrals without the need for complicated formulas or programming.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your patient referral data.
2. Insert a PivotTable: Select the data range, navigate to the 'Insert' tab, and choose 'PivotTable'.
3. Analyze the unique data: Drag 'Patient ID' to Rows and 'Testing Category' to Values, then adjust the calculation settings to view distinct counts seamlessly.

Fully compatible with Microsoft Excel (.xlsx) formats and features.Advanced PivotTable capabilities including accurate data summarization.Lightweight, fast, and capable of handling large datasets smoothly.Free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does a standard PivotTable count show duplicate referrals?

A standard 'Count' setting in a PivotTable simply summarizes the total number of rows (entries) for a specific field, including duplicates. To count only the unique entries, you must utilize the 'Distinct Count' feature via the Data Model.

Can I count distinct categories using a formula instead of a PivotTable?

Yes. In modern spreadsheet versions, you can combine functions like `=COUNTA(UNIQUE(range))` to find distinct values. For older versions without the UNIQUE function, users often rely on array formulas combining SUMPRODUCT and COUNTIFS.

Does the Python pandas solution work for extremely large datasets?

Absolutely. The pandas library in Python is highly optimized for performance. Using `df.groupby().nunique()` can process millions of rows rapidly, often much faster than traditional spreadsheet software.

Excel & Data Problems