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.

##### Table of Contents

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.

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.

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

=COUNTIF(Data!$A$2:$A$14,Summary!A4)

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:

- First, we’ll select an empty cell that will hold our
`COUNTIF`

function.

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. - Next, type the string ‘=COUNTIF(‘ to start the
`COUNTIF`

function.

- 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’.

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. - Next, we’ll need to provide the criteria to determine whether to count a particular entry in the given range.

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. - Use the
**Fill Handle**tool to fill out the rest of the summary table.

We’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!