How to Count Excel Rows Containing Both of Two Specific Values
Question details
The user needs to count the total number of rows in an Excel spreadsheet that simultaneously contain two specific values, such as two distinct card numbers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Performing data analysis where multiple rows representing groups or records must be evaluated to verify if they contain a specific pair of items, and then summing up the total valid rows.
- Observed behavior
- A formula is required to test every row individually for both target values and successfully return the sum of the rows where both tests evaluate to true.
Before applying these formulas, ensure your data range is clearly defined without merged cells, and identify the exact two values or cell references you want to search for across the rows.
Use BYROW and LAMBDA (For Microsoft 365)
An elegant, single-cell dynamic array formula solution available exclusively in Microsoft 365 to test and count rows dynamically without needing extra columns.
Microsoft 365 introduces dynamic array functions like BYROW and LAMBDA, allowing you to iterate over rows and apply custom logic. This approach is highly efficient because it processes the entire array in a single cell.
Click on the specific cell where you want the final count to appear.
Type the formula =SUM(--(BYROW(A2:K54,LAMBDA(r,--SUM(--(r={8;15}))>=2)))) into the formula bar. Replace 'A2:K54' with your actual data range, and '{8;15}' with the two specific card numbers or values you are searching for.
Press the Enter key. Excel will dynamically test each row and instantly output the total sum of rows containing both values.
Use a Helper Column with COUNTIF (For All Excel Versions)
A highly compatible and reliable approach using the COUNTIF function in an additional column to verify both conditions row by row.
Analyze Complex Data Effortlessly with WPS Spreadsheet
Whether you need to use standard COUNTIF functions or advanced array formulas to count rows meeting multiple conditions, WPS Spreadsheet provides powerful data processing capabilities in a lightweight and seamless environment.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the data you wish to analyze.
- 2. Insert a helper column: Select an empty column next to your target data range to serve as your condition checker.
- 3. Apply the COUNTIF logic: Input the formula =--(COUNTIF(A2:K2,8)>0)*--(COUNTIF(A2:K2,15)>0) into the first cell of the helper column.
- 4. Drag to fill: Use the fill handle to drag the formula down to the end of your dataset, evaluating all rows.
- 5. Calculate the total: Use the standard =SUM() function on the helper column to immediately view the total number of rows containing both card numbers.
Frequently Asked Questions
Can I use cell references instead of typing the numbers directly in the formula?
Yes. In the BYROW formula, you can replace the hardcoded array {8;15} with a cell range like N1:N2 that contains your target values. In the COUNTIF helper column method, simply replace the numbers 8 and 15 with $N$1 and $N$2 respectively.
How do I count rows that contain either of the two numbers, but not necessarily both?
To count rows with at least one of the target numbers, you can change the condition in the BYROW formula from >=2 to >=1. If using the helper column method, change the multiplication symbol (*) to an addition symbol (+) between the two COUNTIF statements.
Will this method work if the target numbers are formatted as text?
If your numbers are stored as text instead of numerical values, you must wrap them in double quotation marks within your formula. For example, use {"8";"15"} in your BYROW array or COUNTIF(A2:K2,"8") to ensure the functions recognize them correctly.
