Function Problems

Display Rows Counted by a COUNTIFS Formula in Excel

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

The user wants to display the records that meet the conditions in an Excel COUNTIFS formula.

Display Rows Counted by a COUNTIFS Formula in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Check the COUNTIFS criteria

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.

2
Enter a FILTER formula

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.

3
Review the results

Press Enter to display the matching rows. If Excel shows a #SPILL! error, clear any cells blocking the output area and try again.

Return Matching Rows with Excel FILTER
Keep the ranges aligned: The FILTER source and each criteria range must cover the same rows. The example returns columns A through C for every matching row.
Work with matching records in WPS

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. 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select your Excel workbook.
  2. 2. Check the criteria: Select the COUNTIFS formula cell and note each criteria column and condition in the formula bar.
  3. 3. Enable filtering: Select a cell in the data range, including its headers, and choose Data > Filter.
  4. 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.
Inspect rows matching multiple COUNTIFS conditionsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar column filters to explore worksheet dataWork with spreadsheets in a free, easy-to-use office suite
microsoft office alternative - wps office

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.