Display Rows Counted by a COUNTIFS Formula in Excel
Question details
The user wants to display the records that meet the conditions in an Excel COUNTIFS formula.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses COUNTIFS to count records, and the user wants to see the matching rows.
- Observed behavior
- COUNTIFS returns a count; the desired result is a list of the rows included in that count.
Identify each criteria range and condition in your COUNTIFS formula, along with the columns you want to display. Make sure the source and criteria ranges cover the same rows.
Return Matching Rows with Excel FILTER
Use FILTER to create a list of records that updates when the source data changes.
FILTER is available in Microsoft 365 and Excel 2021 or later. Use an asterisk between conditions to require all conditions to be true.
Select the cell containing COUNTIFS and inspect its formula. For example, =COUNTIFS(B2:B100,">10",C2:C100,"Open") counts rows where column B is greater than 10 and column C is Open.
Select an empty cell with room below and to the right, then enter =FILTER(A2:C100,(B2:B100>10)*(C2:C100="Open"),"No matches"). Replace the ranges and conditions with those from your worksheet.
Press Enter to display the matching rows. If Excel shows a #SPILL! error, clear any cells blocking the output area and try again.

Show Matching Rows with Excel Column Filters
Use column filters when FILTER is unavailable or you want to inspect matches in the original table.
Display COUNTIFS Matches in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and apply column filters that match your COUNTIFS criteria to inspect the counted records.
- 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select your Excel workbook.
- 2. Check the criteria: Select the COUNTIFS formula cell and note each criteria column and condition in the formula bar.
- 3. Enable filtering: Select a cell in the data range, including its headers, and choose Data > Filter.
- 4. Apply matching filters: Open each relevant column's filter arrow and set the same conditions as the COUNTIFS formula. Review the visible rows to see the matching records.

Frequently Asked Questions
Can COUNTIFS display the rows it counts?
COUNTIFS returns a count, not the records themselves. Use FILTER with the same criteria, or apply matching filters through Data > Filter to inspect the rows.
How do I return rows that meet two conditions?
In Excel, multiply the conditions inside FILTER. For example, =FILTER(A2:C100,(B2:B100>10)*(C2:C100="Open"),"No matches") returns rows meeting both conditions.
What can I do if FILTER is not available?
Select your data and choose Data > Filter, then apply the COUNTIFS conditions using the relevant column filter arrows. This lets you inspect matching rows in the original table.
Why does my FILTER formula show a #SPILL! error?
The output area may contain existing data. Clear the cells below and to the right of the formula cell so Excel has room to display the results.




