# How to Use SUMIF From Another Sheet in Excel

This guide will explain how to use SUMIF from another sheet in Excel.

You may want to use the SUMIF function on a range outside the current sheet.

Excel allows you to use data from outside your current sheet using external references.

An external reference refers to a cell or range of cells outside the current worksheet. Similar to regular cell references, the value returned will also change if the referenced cell changes in value.

Let’s take a look at a quick example of a use case where you might need to use an external reference with the `SUMIF` formula.

Suppose you have two sheets in your worksheet. The first worksheet is named ‘Data’, and the second worksheet is named ‘Summary’.

The Data sheet includes a list of purchases. Each purchase has an associated amount and a category it falls under.

We want to set up our summary sheet to provide a breakdown of the total purchase amount of each category. How can we do this in Excel?

Since we want to find the total of a range given certain conditions, we will use the `SUMIF` function. We will write our formula in the summary sheet and use an external reference to retrieve values from the data tab.

We’ll explain how to use external references in the following section. We will tackle how to reference either an individual cell or a range of cells, and how to create a reference to another Excel workbook.

Now that we know when to use external references with the `SUMIF` function, let’s learn how to use it on an actual sample spreadsheet.

## A Real Example of Using SUMIF from Another Sheet in Excel

The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a look at our sample dataset. We have a table with two fields: category and amount.

This table can be found in the first sheet in our workbook. We’ve labeled this sheet ‘Data’ for convenience.

In the ‘Summary’ sheet, we’ve created another table to summarize the total amount purchased for each category.

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

`=SUMIF(Data!\$A\$2:\$A\$17,Summary!B5,Data!\$B\$2:\$B\$17)`

### Creating External References

Let’s try to understand how the formula above works.

We can create an external reference to a cell in another sheet in the current workbook by using the following syntax:

If we want to reference a range of cells, we’ll use the following syntax:

`Sheet_name!First_cell:Last_Cell`

If the worksheet name has spaces or non-alphabetical characters, you must enclose the name in single quotation marks. For example, if we want to reference cell A1 in a sheet named ‘July 2022’, we’ll write it like this:

`=’July 2022’!A1`

What should we do when we must reference cells in another workbook?

If the source workbook is open, we can add the workbook name in square brackets.

Suppose we have a workbook with the filename ‘Annual_Report.xlsx’. We want to retrieve the range B1:B100 in the sheet labeled ‘January’.

We can write this external reference as follows:

`[Annual_Report.xlsx]January!B1:B100 `

If the source workbook is closed, we will have to indicate the entire path to the source workbook.

`C:\Work\Reports\[Annual+Report.xlsx]January!B1:B100 `

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 using `SUMIF` from another sheet in Excel, head over to the next section to read our step-by-step breakdown on how to do it!

## How to Use SUMIF From Another Sheet in Excel

This section will guide you through each step needed to start using `SUMIF` from another sheet in Excel.

Follow these steps to start using external references with the `SUMIF` function:

1. First, select the cell where you want to add the `SUMIF` formula. In this example, we will start with cell C5 in the Summary sheet.
2. Next, type ‘=SUMIF(‘ in the formula bar to start the `SUMIF` function.
3. The first argument of `SUMIF` will determine the range Excel will check to see if the amount should be included in the sum. Since this range is located on another sheet, we must type the sheet name followed by the ‘!’ symbol.
4. Write the cell range you want to reference after the ‘!’ symbol.
5. Next, we’ll use the same syntax to add a reference to the second argument. The second argument will indicate what criteria to use for the `SUMIF` function. In this example, we want to only add cells with the category ‘A’.
6. We’ll add another external reference for the third argument of the `SUMIF` function. This argument will determine the range of values that `SUMIF` will use to calculate the sum.
7. Type the Enter key to evaluate the function.
8. Use the Fill Handle tool to fill out the rest of the summary table. We’ve converted our external references to absolute references to prevent the ranges from changing.

These are all the steps needed to start using `SUMIF` from another sheet in Excel.

This step-by-step guide should provide you with all the information you need to use the `SUMIF` function on a range of values in another sheet.

We’ve explained in detail how to create an external reference you can use with the `SUMIF` function.

The `SUMIF` function is just one example of the many Excel functions you can use in your spreadsheets. 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.

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.

## How to Remove Table Formatting in Excel

This guide will explain how to remove table formatting in Excel using five easy and simple ways.  Excel…

## How To Perform Data Binning in Excel

This guide will discuss how to perform data binning using three simple methods in Excel. Excel is a popular…

## How to Average Only Positive or Negative Numbers in Excel

You can use the AVERAGEIF function to average only positive or negative numbers in Excel. Computing the average…

## How to Interpolate Missing Values in Excel

This guide will explain how to interpolate missing values in Excel using three easy and simple ways. Since…