Function Problems

How to Count Unique Users by Branch and Date Range in Excel

Camila Milosovich Oct 12, 2026 869 views

Question details

The user needs a way to calculate the distinct count of usernames corresponding to specific branches and exact date ranges without using any helper columns.

How to Count Unique Users by Branch and Date Range in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing project data to determine how many unique individuals from specific branches participated during specific time periods (e.g., days 1-15 versus days 16-31).
Observed behavior: Duplicate username entries are currently making standard counting methods inaccurate. A formula is needed to filter multiple criteria first and then count only distinct users.

Before you start

Ensure your dataset is formatted as an official Excel Table (e.g., Table1) so that column references update automatically, and verify that your date column contains recognized date values rather than text.

Solution 1Recommended

Use UNIQUE and FILTER Functions for Dynamic Distinct Counting

This method combines dynamic array functions to seamlessly filter records by multiple conditions, extract the unique names, and count them without needing helper columns.

By nesting FILTER inside UNIQUE, you isolate only the usernames that meet both your date and branch criteria. Wrapping the entire expression in COUNTA returns the final distinct count.

Set up your criteria cells

In your summary area, enter the branch identifier (e.g., 'STX') in cell A2. Enter your Start Date in cell B1 and your End Date in cell B2.

Construct the FILTER array

Select the cell for your result and begin typing `=FILTER(Table1[Username], (LEFT(Table1[Username],3)=A2)*(Table1[Date]>B$1)*(Table1[Date]<B$2))`. This filters the username column where the first 3 letters match your branch and the dates fall between your specified boundaries.

Wrap with UNIQUE and COUNTA

Modify the formula to wrap the FILTER function like this: `=COUNTA(UNIQUE(FILTER(Table1[Username], (LEFT(Table1[Username],3)=A2)*(Table1[Date]>B$1)*(Table1[Date]<B$2))))`. Press Enter.

Adjust references

If your exact table name or column names differ, simply highlight the actual Username and Date columns in your worksheet to automatically update the formula syntax.

Use UNIQUE and FILTER Functions for Dynamic Distinct Counting

Handling Empty Filter Results: If the FILTER function finds no matching records for a specific date range, it may return a #CALC! error, which COUNTA counts as '1'. To return an exact '0', wrap your formula in an IFERROR or IFNA function.

Seamless Spreadsheet Solution

Use WPS Spreadsheet for Advanced Array Formulas

WPS Office provides full support for modern dynamic array functions like UNIQUE and FILTER, allowing you to seamlessly analyze complex datasets and count distinct variables without helper columns.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the user log data.
2. Create your summary table: Set up your target branch prefixes and date boundaries in a new worksheet or alongside your main table.
3. Apply the dynamic array formula: Enter the `=COUNTA(UNIQUE(FILTER(...)))` formula into the target cell, and WPS Spreadsheet will instantly calculate the distinct counts.
4. Save your work: Save the file seamlessly in .xlsx format to retain full functional compatibility with Microsoft Excel users.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in support for dynamic arrays including UNIQUE, FILTER, and SORT.Lightweight architecture ensures fast calculations even with massive datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Can I count unique users if my version doesn't support the UNIQUE function?

Yes. If you are using an older version of Excel, you can insert a Pivot Table and check the 'Add this data to the Data Model' box during setup. This enables the 'Distinct Count' option in the Value Field Settings.

Why does my COUNTA formula return 1 when there are zero matches?

When the FILTER function finds no matches, it returns a #CALC! error. The UNIQUE function processes this error, and COUNTA counts the error text as 1 item. You can use an IF function to check for errors before counting.

How can I filter by exact branch names instead of a 3-letter prefix?

Instead of using `LEFT(Table1[Username],3)=A2` in your formula, directly reference a dedicated branch column (if available) using `(Table1[Branch]=A2)`. This provides more accurate filtering if your username formats vary.

Does the UNIQUE function distinguish between uppercase and lowercase letters?

No, the UNIQUE function is case-insensitive. Entries like 'stx.user1' and 'STX.USER1' will be treated as the exact same identical user and counted only once.

Excel & Data Problems