Formula Errors

How to Fix #REF! Error When Counting Multiple Criteria in Excel

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to count players by position and team but encounters a formula error when attempting to do so.

Product: Excel
Device & OS: not provided
Scenario: Counting data across a dataset based on multiple criteria, such as player position and team affiliation.
Observed behavior: A #REF! error is returned because the user is using INDEX and MATCH, which return a single value rather than a cell range required for counting.

Before you start

Verify that your data ranges do not contain merged cells and that the worksheet names referenced in your formulas match exactly what is written on the sheet tabs.

Solution 1Recommended

Use the COUNTIFS Function Instead of INDEX and MATCH

Switching to the COUNTIFS function resolves the issue because it is specifically designed to count cells matching multiple conditions without returning reference errors.

The INDEX and MATCH functions are powerful lookup tools designed to return a specific cell's value. However, when embedded in a formula expecting an array or continuous range for counting, they fail to provide the correct data type, resulting in a #REF! error.

The COUNTIFS function natively handles multiple conditions and ranges simultaneously. It is the standard and most stable method for counting data based on intersecting criteria, such as mapping players to specific positions and teams.

Select the target cell

Click on the cell in your summary table where you want the first count result to appear.

Enter the COUNTIFS formula

Type =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2). For this scenario, use a formula similar to =COUNTIFS(FTX_Players!$D:$D, F$2, FTX_Players!$F:$F, $E3).

Apply absolute and mixed references

Ensure you lock the column and row references correctly. Use $D:$D to lock the criteria columns, F$2 to lock the header row, and $E3 to lock the header column so the formula adjusts properly.

Copy the formula across the table

Drag the fill handle (the small square at the bottom-right of the cell) across the columns and down the rows to populate the rest of the table.

Calculate overall totals

Use the =SUM() function at the end of your rows or columns to calculate the grand totals based on the newly generated counts.

Pro Tip on Formula Referencing: Pressing the F4 key on your keyboard while editing a formula will automatically cycle through absolute, mixed, and relative cell references (e.g., changing A1 to $A$1, A$1, or $A1).

Efficient Formula Processing

Use WPS Spreadsheet to Handle Complex Formulas with Ease

WPS Spreadsheet is a powerful, free tool that supports all standard formulas, including COUNTIFS, INDEX, MATCH, and SUM. It offers intelligent formula error checking and auto-completion to help you build complex reports efficiently without running into #REF! errors.

1. Open your workbook: Launch WPS Office and open your spreadsheet file containing the player data.
2. Use the Function Wizard: Click the 'fx' button next to the formula bar to open the Insert Function dialog, then search for 'COUNTIFS'.
3. Input arguments visually: Follow the dialog box prompts to individually select your criteria ranges and condition cells, eliminating manual syntax errors.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Intelligent formula suggestions and real-time error highlighting.Free, lightweight, and capable of processing large datasets seamlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why do INDEX and MATCH cause a #REF! error when counting?

INDEX and MATCH are designed to return a specific cell's value or a single reference. If they are nested inside another function that expects a continuous data range (like a counting function), the formula cannot evaluate the range properly, resulting in a reference (#REF!) error.

What is the syntax for the COUNTIFS function?

The syntax is =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...). Every additional criteria range must have the exact same number of rows and columns as the first criteria range, otherwise the formula will return a #VALUE! error.

How do I fix a #REF! error if I accidentally delete a row or column?

A #REF! error often occurs when a formula explicitly references a cell, row, or column that has been deleted. You can undo the deletion (Ctrl+Z) or manually edit the formula to point to the new, correct range.

Can I use COUNTIFS across different worksheets?

Yes. You can reference ranges on other sheets by prefixing the cell range with the sheet name followed by an exclamation mark, such as Sheet2!$A$1:$A$100.

Excel & Data Problems