Function Problems

How to Filter an Excel Table by Names Across Two Columns

Elise WilliamsElise Williams Nov 11, 2026 870 views

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.

How to Filter an Excel Table by Names Across Two 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.
Before you start

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.

Solution 1

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.

1
Set up a selection cell

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.

2
Add a helper column

Insert a new column next to your table and name it 'Filter Check'.

3
Enter the COUNTIF formula

In the first cell of the helper column, enter the formula: =COUNTIF(tblData[@[SALES SPLIT]:[SALES SPLIT 2]],$K$1)>0

4
Apply the filter

Click the filter dropdown arrow on your new 'Filter Check' column header and check only the 'TRUE' option to display the matching transactions.

Structured References: Ensure that 'tblData' matches the actual name of your table and 'SALES SPLIT' matches your actual column headers.
WPS Spreadsheet Solution

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. 1. Open your dataset: Launch WPS Spreadsheet and open your transaction or sales report dataset.
  2. 2. Insert a helper column: Add a new column beside your salesperson columns to act as the filter logic.
  3. 3. Apply the filtering formula: Type the SUMPRODUCT or COUNTIF formula to check both columns against your target names.
  4. 4. Filter the results: Navigate to the 'Data' tab, click 'AutoFilter', and display only the 'TRUE' values to get your final list.
Easily processes complex helper-column formulas for multi-column filtering.Fully compatible with Microsoft Excel (.xlsx) formats and standard formulas.Lightweight, fast, and completely free to use for everyday data analysis.
microsoft office alternative - wps office

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.