Pivot Table Issues

How to Filter PivotTable Items with AND Logic for Multiple Projects

Maira Mehtab Sep 22, 2026 872 views

Question details

The user needs to filter a PivotTable to display only the items (like people's names) that are associated with multiple specific projects simultaneously, requiring AND logic rather than the standard OR logic.

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering a dataset of personnel and projects to identify individuals who have worked on both Project 1 and Project 3 at the same time.
Observed behavior: When selecting multiple projects in a standard PivotTable filter, the system applies OR logic, showing individuals who worked on either project instead of strictly those who worked on both.

Before you start

Ensure your raw data is organized in a flat, tabular format without empty rows or columns, and clearly identify the specific criteria you need to match simultaneously.

Solution 1Recommended

Use a Helper Column with Formula

Create an additional column in your source data using logical formulas to calculate if a person meets both criteria, then filter the PivotTable based on this new column.

Since standard PivotTable filters use OR logic, the most straightforward approach for standard spreadsheet users is to handle the AND logic in the source data before pivoting. By using the COUNTIFS function, you can check for the existence of multiple conditions for the same entity.

Add a helper column

Insert a new column next to your raw data table and name it 'Matches Both'.

Enter the logical formula

Assuming column A contains Names and column B contains Projects, enter a formula in the first data row (e.g., C2): =IF(AND(COUNTIFS(A:A, A2, B:B, "Project 1")>0, COUNTIFS(A:A, A2, B:B, "Project 3")>0), "Yes", "No"). Drag this formula down to apply it to all rows.

Update the PivotTable

Click inside your existing PivotTable, go to the PivotTable Options tab, and click 'Refresh' to load the new helper column.

Filter by the helper column

Drag the newly created 'Matches Both' field into the Filters area of your PivotTable, and select 'Yes' from the dropdown to display only the names working on both projects.

Solution 2

Use Power Pivot and DAX Measures

For advanced datasets and users with Data Model capabilities, use Power Pivot to create a DAX measure that counts distinct project matches.

Advanced Pivot Tables in WPS

Master Data Filtering with WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools, including extensive logical functions and robust PivotTable features, allowing you to easily handle complex data intersections and custom filtering requirements.

1. Open your data file: Launch WPS Spreadsheet and open your project dataset.
2. Add your logical criteria: Insert a helper column and use WPS Spreadsheet's built-in COUNTIFS formula to flag rows meeting your specific AND logic.
3. Insert PivotTable: Go to the 'Insert' tab, click 'PivotTable', and select your updated data range.
4. Apply the custom filter: Drag your helper column into the 'Filter' box in the PivotTable setup pane to isolate your target records instantly.

Fully compatible with Microsoft Excel (.xlsx) file formatsSupports advanced logical formulas like COUNTIFS, AND, and IFIntuitive PivotTable creation and customizationFree, lightweight, and easy-to-use alternative for complex data tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why do standard PivotTable filters use OR logic instead of AND?

By design, when you check multiple items within a single PivotTable field filter, the system assumes you want to aggregate all data belonging to any of those selected items. This maximizes the result set using OR logic.

Can I use the Advanced Filter feature instead of a PivotTable?

Yes. If you simply need a list of names and not summarized totals, you can use the Advanced Filter located in the Data tab. By setting up a criteria range with conditions on the same row, it extracts records that meet multiple criteria simultaneously.

Will my helper column update automatically when I add new projects?

The helper column formula will calculate automatically for new rows if you drag the formula down or use a formal Table format. However, you must always manually right-click your PivotTable and select 'Refresh' to see the updated results.

Excel & Data Problems