How to Use COUNTIF From Another Sheet in Excel

This guide will explain how to use the COUNTIF function from another sheet in Excel.

The COUNTIF function is useful when you want to know how many cells in a given range fit a particular criteria.

We can use the COUNTIF function to count cells that meet a certain condition. For example, you might want to use COUNTIF to see how many students scored above 90% in a recent test.

Let’s take a look at another example of a scenario where we might need to use COUNTIF from another sheet in Excel.

Suppose you have a table of orders in one spreadsheet. Each order comes with the customer’s name, their location, as well as the item and item quantity of their order.

You want to summarize where all these orders are coming from. We can use the COUNTIF function in another sheet to calculate the number of orders coming from one particular city.

Now that we know when to use COUNTIF from another sheet in Excel, let’s learn how to use it and work on an actual sample spreadsheet.



A Real Example of Using COUNTIF from Another Sheet

The following section provides a simple example of how to use COUNTIF from another sheet. We will also explain the formulas and tools used in this example.

First, let’s take a look at our sample data. We have a dataset of entries with a type and ID column. We want to create a summary in another sheet that counts how many entries of each type exist in our dataset.

sample data to count


In the example below, we created a summary table that uses the COUNTIF function to count the number of entries that fall under each type.

summary using COUNTIF from another sheet

To get the values in Column B, we just need to use the following formula:


We’ve specified the sheet name for each of our references since we are not working with two different sheets.

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out the using COUNTIF from another sheet, head over to the next section to read our step-by-step breakdown on how to do it!



How to Use COUNTIF From Another Sheet in Excel 

This section will guide you through each step needed to start using COUNTIF from another sheet. You’ll learn how to set up the COUNTIF function and how to reference cells from outside your current sheet.

Follow these steps to start using the COUNTIF function to count data from another sheet:

  1. First, we’ll select an empty cell that will hold our COUNTIF function.
    select an empty cell to use COUNTIF from another sheet

    In this example, we’ll use cell B2. We’ve created a table that will help us tally the number of entries in our table by type.
  2. Next, type the string ‘=COUNTIF(‘ to start the COUNTIF function.
    use COUNTIF function
  3. You must specify the range to count on as the first argument of COUNTIF.
    Excel allows you to refer to cells and cell ranges outside the current sheet. The cell reference should have the sheet name followed by a ‘!’ character. In this example, we’ve selected the range A2:A14 in the Data tab by using the cell reference ‘Data!A2:A14’.
    select range to count values from
    An easy way to select data from outside the current sheet is to use your cursor to switch to another sheet while typing a formula in the Formula bar. Excel will add the cell reference of any selection you create using your cursor.
  4. Next, we’ll need to provide the criteria to determine whether to count a particular entry in the given range.
    add criteria for countif function
    In this particular example, we will add a cell reference to the type indicated in column A of the summary table. Hit the
    Enter key to evaluate the function.
  5. Use the Fill Handle tool to fill out the rest of the summary table.
    COUNTIF from another sheetWe’ve converted our range into an absolute reference to prevent it from changing as we drag down the formula.

These are all the steps needed to use the COUNTIF function from another sheet in Excel. 



This step-by-step guide should provide you with all the information you need to begin using the COUNTIF function from another sheet.

You should now understand how to select data from another sheet and how to use that data in a COUNTIF function.

Using the COUNTIF function for summarizing data is just one way you can use Excel functions to make sense of your data.

Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel. With so many other Excel functions available, you can find one appropriate for your use case.

For example, you can use the COUNTIFS function to keep track of how many entries in a table fall within a certain date range. You may read our guide to learn how to use the COUNTIFS function for this purpose.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.


Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like