Formula Errors

Fix PIVOTBY #VALUE Error with Multiple Filter Conditions in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to apply multiple filter conditions in an Excel PIVOTBY formula but encounters a #VALUE! error when attempting to add a second criteria.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a dynamic array formula using PIVOTBY to summarize data, which requires filtering the results by multiple specific conditions simultaneously (e.g., Employee and Location).
Observed behavior: The formula executes successfully with one condition, but returns a #VALUE! error when multiple conditions are combined incorrectly without proper array syntax.

Before you start

Ensure your dataset contains clear column headers and no merged cells. Familiarize yourself with basic Boolean logic (using ones and zeros to represent True/False), as it is essential for combining multiple criteria in dynamic array formulas.

Solution 1Recommended

Apply Boolean AND Logic with Parentheses and Multiplication

Enclosing each filter condition in its own set of parentheses and multiplying them together ensures Excel processes the criteria as an array of TRUE/FALSE values, instantly resolving the #VALUE! error.

In Excel array formulas, the standard AND() function cannot evaluate arrays row-by-row. Instead, you must use Boolean math where the asterisk (*) acts as the AND operator.

Select the formula cell

Click on the cell containing your broken PIVOTBY formula to make it active.

Edit the filter_array argument

Navigate to the formula bar and locate the section of your formula handling the filter conditions.

Wrap the first condition

Place your first condition entirely inside parentheses. For example: (Coffee[Employee]=$S$2).

Add the multiplication operator

Type an asterisk (*) immediately after the closing parenthesis of your first condition.

Wrap and add the second condition

Add your second condition inside parentheses, resulting in a sequence like: (Coffee[Employee]=$S$2)*(Coffee[Location]=$Q$2).

Execute the formula

Press Enter to apply the updated formula. The PIVOTBY function will now successfully filter by both conditions without throwing a #VALUE! error.

Adding More Conditions: You can chain as many criteria as needed using the exact same format. Just add another *(New_Condition) to the end of your filter array.

Solution 2

Use the Plus Sign (+) for OR Logic

If your goal is to return data when either one condition OR the other condition is met, you must use the plus sign (+) instead of an asterisk.

Advanced Data Analysis in WPS

Easily Summarize and Filter Data with WPS Spreadsheet

While beta array formulas like PIVOTBY can be prone to syntax errors and complex Boolean math requirements, WPS Spreadsheet provides an intuitive, built-in PivotTable feature alongside robust dynamic array functions. You can summarize, filter, and analyze large datasets with multiple conditions effortlessly—without worrying about missing brackets.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw data.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon and click the 'PivotTable' button.
3. Select data range: Highlight your entire data table and choose whether to place the PivotTable on a new or existing worksheet.
4. Apply multiple filters seamlessly: In the PivotTable pane, drag fields like 'Employee' and 'Location' into the Filters or Rows areas to instantly apply multiple criteria without writing a single formula.
5. Summarize values: Drag your numerical field (e.g., 'Quantity Sold') into the Values area to view your accurate, aggregated data.

100% compatible with Microsoft Excel file formats (.xlsx)User-friendly PivotTable interface for drag-and-drop multiple filteringNative support for advanced array formulas including FILTER, UNIQUE, and SUMIFSLightweight, fast, and free to use even with heavy datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does the PIVOTBY formula require parentheses for each condition?

In dynamic array formulas, mathematical operators (like multiplication) take precedence over logical operators (like equals). Parentheses force the software to evaluate the equality (e.g., Location="NY") first, converting it to TRUE or FALSE, before multiplying the arrays together.

Can I use the AND() or OR() functions inside PIVOTBY instead?

No. The standard AND() and OR() functions aggregate an entire array into a single TRUE or FALSE result, which breaks array-based formulas. You must use Boolean math (* for AND, + for OR) to return an array of results for each individual row.

What is the maximum number of filter conditions I can add to PIVOTBY?

There is no strict syntax limit to the number of conditions you can chain together using the *(Condition) method. However, excessively long arrays on massive datasets may slightly impact your workbook's calculation performance.

Does this Boolean bracket logic apply to other array formulas?

Yes, this logic is standard practice in modern spreadsheet environments. The exact same *(Condition) method is used for functions like FILTER, SORTBY, XLOOKUP, and the classic SUMPRODUCT formula.

Excel & Data Problems