How to Use XLOOKUP with Multiple Criteria in Excel (COUNTIFS Style)
Question details
The user wants to combine employee trade lookups with working-status criteria to count matching employees without relying on intermediate helper spill ranges.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Counting data based on multiple arrays, such as matching a specific employee trade and a working status across multiple columns, without generating helper columns.
- Observed behavior
- A standard COUNTIFS function requires hard ranges and cannot natively process the array generated by XLOOKUP in memory, creating a need for an alternative formula approach.
Ensure that your data ranges are identical in size when multiplying arrays, and verify that your version of Excel or WPS Spreadsheet supports dynamic arrays and the XLOOKUP function.
Use SUMPRODUCT and XLOOKUP for Multiple Array Criteria
Combine SUMPRODUCT and XLOOKUP to evaluate both the trade condition and the working-status condition in a single formula without helper columns.
Because XLOOKUP returns an array in memory, COUNTIFS cannot process it directly. SUMPRODUCT overcomes this limitation by evaluating the Boolean arrays (TRUE/FALSE) generated by multiple conditions and multiplying them to count the exact matches.
Click on the cell where you want the final employee count to appear.
Type the formula combining both conditions. For example: =SUMPRODUCT((XLOOKUP(A2:A30,TradeTable!A:A,TradeTable!B:B,"")="Electrician")*(C2:G30="D")).
Press Enter. The formula multiplies the condition arrays, converting TRUE to 1 and FALSE to 0, and sums the results to give you the total count.
Use SUM and TRANSPOSE for Horizontal Status Counting
If your employee status data spans horizontally across columns, use SUM combined with TRANSPOSE to align the criteria arrays properly.
Use MMULT for Matrix Multiplication
For more advanced array calculations across rows and columns, MMULT can perform matrix multiplication on the two criteria arrays to return the count.
Master Advanced Array Formulas in WPS Office
WPS Spreadsheet provides full native support for modern functions like XLOOKUP, SUMPRODUCT, and dynamic arrays, enabling you to build complex, multi-criteria calculations effortlessly.
- 1. Download and Install: Download WPS Office for free and open your spreadsheet document.
- 2. Access Advanced Functions: Select an empty cell and type =XLOOKUP or =SUMPRODUCT to reveal the syntax helper.
- 3. Enter Your Criteria: Combine the functions seamlessly using standard Excel array logic to instantly compute your results.
Frequently Asked Questions
Why is my XLOOKUP formula returning a #SPILL! error?
A #SPILL! error occurs when an array formula attempts to output results across multiple cells, but the adjacent cells contain data. Ensure the spill range is completely empty.
Can I use COUNTIFS instead of SUMPRODUCT with XLOOKUP?
No. The COUNTIFS function requires physical cell ranges. Because XLOOKUP generates a dynamic array in memory rather than a hard range, you must use SUMPRODUCT or SUM to evaluate the resulting array.
Is XLOOKUP available in older versions of Excel?
XLOOKUP is only available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version, you must rely on the traditional INDEX and MATCH combination.
What does the double dash (--) do in the MMULT formula?
The double unary operator (--) is used to coerce Boolean arrays. It converts TRUE and FALSE results generated by your conditional statements into 1s and 0s, which is required for mathematical functions like MMULT and SUMPRODUCT.
