How to Count Rows Containing Multiple Specific Numbers in Excel
Question details
The user needs to count the total number of rows across multiple columns (e.g., six columns) that simultaneously contain multiple specific numbers, with the flexibility to change criteria or add more conditions for a dataset of approximately 2,000 rows.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing a large multi-column dataset to find and count the exact number of rows that meet two or more numeric criteria anywhere within the row.
- Observed behavior
- Requires a robust formula or data processing method capable of evaluating multiple columns per row and returning an accurate total row count matching all specified values.
Ensure your dataset does not contain merged cells in the target range and that all numerical values are formatted as numbers rather than text strings, as text formats will cause these mathematical arrays to fail.
Use the SUMPRODUCT and MMULT Formula Combination
This highly adaptable array formula is ideal for checking multiple criteria across multiple columns within a single row.
By combining SUMPRODUCT, MMULT, ISNUMBER, and MATCH, you can create a logical matrix that evaluates whether your target numbers exist in each row, counting only the rows where all required criteria are met.
Type the numbers you want to find into dedicated cells, for example, place your two criteria in cells I2 and I3.
Select an empty cell for your result and input the formula: =SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A2:F7,I2:I3,0)),{1;1;1;1;1;1})>=2))
Ensure the inline array {1;1;1;1;1;1} has exactly the same number of '1's as there are columns in your data range (6 columns in this example). If you expand your criteria to three numbers (e.g., I2:I4), change the final logical check from >=2 to >=3.
Use the FREQUENCY Array Formula
An alternative formula method using FREQUENCY, suitable when working with specific, hardcoded criteria counts across rows without matrix multiplication.
Unpivot and Count using Power Query
A scalable, non-formula alternative that is perfect for very large datasets where heavy array formulas might slow down spreadsheet performance.
Efficiently Analyze Complex Datasets with WPS Spreadsheet
WPS Office provides robust, high-performance formula support, including advanced array functions like MMULT, SUMPRODUCT, and FREQUENCY, allowing you to seamlessly execute complex multi-criteria row counts.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and load your multi-column dataset.
- 2. Input the multi-criteria formula: Click an empty cell and paste the SUMPRODUCT and MMULT array formula exactly as you would in Excel.
- 3. Calculate instantly: Press Enter to instantly evaluate your 2,000+ rows and retrieve the accurate criteria count.
Frequently Asked Questions
How do I modify the SUMPRODUCT and MMULT formula for 3 criteria?
To look for three different numbers, expand your criteria range from two cells to three (e.g., I2:I4). Then, change the final logical condition at the end of the formula from >=2 to >=3 to ensure all three values are found in the row.
Why is my MMULT formula returning a #VALUE! error?
This usually happens because the inline array size does not match your column count. The array {1;1;1;1;1;1} must contain exactly the same number of elements (1s) as there are columns in your data range. If you have 8 columns, you need eight 1s.
Does the FREQUENCY method work for counting text criteria?
No, the FREQUENCY function strictly evaluates numeric values. If your criteria include text strings, you must use the SUMPRODUCT and MMULT combination, or use nested COUNTIFS functions depending on your data structure.
Will using array formulas slow down my spreadsheet?
Array formulas like MMULT evaluate multiple arrays in memory, which can become resource-intensive on datasets with tens of thousands of rows. For ~2,000 rows it should be fine, but if you notice calculation lag, the Power Query unpivot method is a more scalable alternative.
