COUNTIF From Another Sheet in Excel

To COUNTIF from another sheet, write the sheet name and an exclamation mark in front of the range, like =COUNTIF(Orders!C2:C8,"Shipped"). Everything else about COUNTIF stays the same. This guide shows how to build the reference by clicking, handle sheet names with spaces, count across several tabs and fix the errors that only appear when the data lives somewhere else.

How to COUNTIF from another sheet#

A reference to another sheet is the sheet name, an exclamation mark, then the range. COUNTIF accepts it like any other range:

=COUNTIF(SheetName!range, criteria)

All the examples use an Orders sheet with this data in A1:D8:

On a separate Summary sheet, this counts the shipped orders:

=COUNTIF(Orders!C2:C8,"Shipped")

The result is 4 (orders 1001, 1003, 1005 and 1006). The criteria works exactly as it does on one sheet: text in double quotes, comparisons like ">100" in quotes too. If COUNTIF itself is new to you, start with the complete COUNTIF guide.

How to build the reference by clicking#

You don't have to type the sheet name. Excel writes it for you if you click the other tab while the formula is open:

1. On the Summary sheet, click the cell for the result and type =COUNTIF(.
2. Click the Orders tab at the bottom of the window.
3. Select C2:C8. The formula bar now shows =COUNTIF(Orders!C2:C8.
4. Type ,"Shipped") and press Enter.

Excel returns you to the Summary sheet with the answer. Don't click back to the Summary tab before you press Enter, or Excel may add a reference to the wrong sheet. To move between tabs with the keyboard, use Ctrl+Page Down and Ctrl+Page Up on Windows, or Option+Right Arrow and Option+Left Arrow on a Mac. The worksheets and cross-sheet references lesson practises this.

Sheet names with spaces or symbols#

If the sheet name contains spaces, hyphens or other symbols, wrap it in single quotes:

=COUNTIF('Q1 Orders'!C2:C8,"Shipped")

The quotes go around the sheet name only, and the exclamation mark comes after the closing quote. When you build the reference by clicking, Excel adds the quotes for you. If you rename a sheet later, Excel updates every formula that points to it.

The general rules for sheet references, including whole-column references and tables, are in the guide on how to reference another sheet in Excel.

Use criteria from the current sheet#

The range can be on one sheet and the criteria on another. This is how you build a summary table. On the Summary sheet, type the statuses Shipped, Pending and Cancelled in A2:A4. In B2, type:

=COUNTIF(Orders!$C$2:$C$8,A2)

Fill it down to B4 (Ctrl+D on Windows, ⌘D on a Mac) to get 4, 2 and 1. The $ signs lock the range on the Orders sheet so it doesn't slide down as you fill. A2 has no $, so it moves to A3 and A4.

Comparisons work across sheets too. To count orders over 100:

=COUNTIF(Orders!D2:D8,">100")

The result is 4 (120, 240, 150 and 310). See COUNTIF greater than a number for more comparison criteria, and COUNTIF for cells that contain text for partial matches. With two conditions, COUNTIFS works the same way: =COUNTIFS(Orders!B2:B8,"Patel",Orders!C2:C8,"Shipped") returns 1.

How to count across several sheets#

COUNTIF can't use a 3-D reference, the kind that spans tabs like Jan:Mar!C2:C50. That formula returns #VALUE!. Instead, add one COUNTIF per sheet:

=COUNTIF(Jan!C2:C50,"Shipped")+COUNTIF(Feb!C2:C50,"Shipped")+COUNTIF(Mar!C2:C50,"Shipped")

This is long but clear, and each part can be checked on its own. For many monthly tabs, a cleaner design is one data sheet with a Month column. Then a single COUNTIFS does the job: =COUNTIFS(Data!B:B,"Mar",Data!C:C,"Shipped"). If your data is in an Excel table named Orders, you can skip the sheet name entirely: =COUNTIF(Orders[Status],"Shipped") works from any sheet.

COUNTIF from another workbook#

You can point COUNTIF at another file, and Excel adds the file name in square brackets:

=COUNTIF([Sales.xlsx]Orders!$C$2:$C$8,"Shipped")

This works while Sales.xlsx is open. When it is closed, COUNTIF returns #VALUE!, because COUNTIF, COUNTIFS, SUMIF and SUMIFS only read ranges in open workbooks. Microsoft documents this on the COUNTIF page. You have two fixes:

1. Open the source workbook, then recalculate.
2. Use SUMPRODUCT, which reads closed workbooks: =SUMPRODUCT(--([Sales.xlsx]Orders!$C$2:$C$8="Shipped")). The test inside the brackets gives TRUE or FALSE for each row, and -- turns them into 1s and 0s to add up.

Why COUNTIF from another sheet returns 0 or an error#

Most problems come from the reference, not the criteria. Check these:

• #REF! means the sheet was deleted. Undo with Ctrl+Z (Windows) or ⌘Z (Mac), or rebuild the reference.
• A file window opens when you press Enter: Excel can't find a sheet with that name and thinks you mean another file. Fix the spelling or add the single quotes.
• #VALUE! means a closed source workbook or a 3-D reference, as above.
• 0 when you expect more usually means the criteria doesn't match exactly. Look for extra spaces or a different spelling, and check the range covers every row. A range that stops at row 7 drops order 1007.
• A count of filled cells is easier with "<>"; see COUNTIF for non-blank cells.

The COUNTIF function guide is a quick reference for every criteria type.

Practise it#

Try the exercise on this page, then practise COUNTIF in a live spreadsheet, where you type each formula and it is checked instantly. When you're comfortable, move on to the worksheets and cross-sheet references lesson to use other tabs in real formulas.