How to Filter an Excel Table by Names Across Two Columns
Question details
The user needs to filter an Excel data table to display transactions where a selected salesperson (or multiple salespeople) appears in either of two split sales columns.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering monthly transaction data where sales splits occur across two different columns.
- Observed behavior
- The user requires a streamlined formula or logic to accurately identify and filter rows whenever a target name matches either of the two salesperson fields.
Ensure your dataset is formatted as an official Excel Table (Ctrl+T) so that your formulas can use structured references, which automatically update when new data is added.
Use a Helper Column with COUNTIF for a Single Name
A quick method to filter rows containing a specific salesperson in either of the two columns using a straightforward COUNTIF formula.
If you only need to filter your table by one specific salesperson at a time, you can create a helper column combined with a data-validation dropdown.
Create a drop-down list or type the name of the salesperson you want to filter by in a specific cell, for example, cell K1.
Insert a new column next to your table and name it 'Filter Check'.
In the first cell of the helper column, enter the formula: =COUNTIF(tblData[@[SALES SPLIT]:[SALES SPLIT 2]],$K$1)>0
Click the filter dropdown arrow on your new 'Filter Check' column header and check only the 'TRUE' option to display the matching transactions.
Use SUMPRODUCT and COUNTIF for Multiple Names
Ideal for end-of-month reporting where you need to filter transactions for a designated list of multiple sales representatives simultaneously.
Use Dynamic Array Formulas (Microsoft 365)
A modern approach that filters the dataset dynamically into a new location without requiring any helper columns.
Filter Complex Tables Easily with WPS Spreadsheet
WPS Spreadsheet natively supports advanced filtering capabilities and complex array formulas, including SUMPRODUCT and COUNTIF logic. You can easily set up criteria tables and helper columns to streamline your monthly sales reporting.
- 1. Open your dataset: Launch WPS Spreadsheet and open your transaction or sales report dataset.
- 2. Insert a helper column: Add a new column beside your salesperson columns to act as the filter logic.
- 3. Apply the filtering formula: Type the SUMPRODUCT or COUNTIF formula to check both columns against your target names.
- 4. Filter the results: Navigate to the 'Data' tab, click 'AutoFilter', and display only the 'TRUE' values to get your final list.

Frequently Asked Questions
Why is my SUMPRODUCT formula returning an error when filtering multiple names?
This usually happens if your criteria range (tblCriteria[SalesRep]) includes blank cells or error values. Ensure your criteria table only contains valid, populated salesperson names.
Can I filter across two columns without using helper columns in older versions of Excel?
Yes. You can use the 'Advanced Filter' feature. Set up a criteria range with your two column headers (e.g., 'SPLIT SALES' and 'SPLIT SALES 2'). Place the target name under the first header on one row, and under the second header on the next row below it to simulate an 'OR' condition, then run the Advanced Filter from the Data tab.
Does this method work with text that has trailing spaces?
Formulas that look for exact matches might fail if there are hidden trailing spaces in either your data table or your criteria list. To fix this, use the TRIM function to clean your data, or ensure your data validation dropdowns strictly match the table records.





