Function Problems

How to Use SUMIF to Sum Data from Another Sheet in Excel

Aamir Naveed Akram Sep 30, 2026 871 views

Question details

The user needs to sum values from a specific column in a different Excel worksheet based on a matching criteria in another column.

How to Use SUMIF to Calculate Data from Another Sheet in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a summary sheet that aggregates project costs from a detailed data sheet based on specific project codes.
Observed behavior: The goal is to successfully write a SUMIF formula that correctly references data ranges in another worksheet without formula errors.

Before you start

Ensure that both your summary sheet and your source data sheet are in the same workbook. It is helpful to take note of the exact name of your data source tab (e.g., "Sheet3") before you start typing your formula.

Solution 1Recommended

Use the SUMIF Formula Across Worksheets

Apply the SUMIF function to evaluate a criteria range on another sheet and sum the corresponding values.

The SUMIF function is perfect for adding up values based on a single condition. When pulling data from another sheet, you simply need to include the sheet name followed by an exclamation mark (!) before your cell ranges.

Select the target cell

Click on the cell in your summary sheet (e.g., Sheet1) where you want the calculated total to appear.

Enter the SUMIF formula

Type the formula =SUMIF(Sheet3!D1:D100, 4545, Sheet3!G1:G100) and press Enter. This formula checks rows 1 through 100 in Column D of Sheet3 for the value 4545, and sums the corresponding values in Column G.

Use a dynamic cell reference

Instead of hardcoding the project code into the formula, replace '4545' with a cell reference from your current sheet (e.g., A2). The formula becomes =SUMIF(Sheet3!D1:D100, A2, Sheet3!G1:G100). This allows the sum to update automatically if you change the project code in cell A2.

Formula Breakdown: In this formula, Sheet3!D1:D100 is the criteria range, 4545 (or A2) is the condition, and Sheet3!G1:G100 is the range containing the numbers to sum.

Use WPS Spreadsheet to Manage Cross-Sheet Formulas Easily

WPS Spreadsheet provides full support for advanced functions like SUMIF, IF, and VLOOKUP. You can easily reference other sheets using its intuitive point-and-click formula builder, removing the need to manually type sheet names.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the .xlsx file containing your project data.
2. Click the Insert Function button: Select the cell where you want your result, then click the 'fx' icon located right next to the formula bar.
3. Select the SUMIF function: Search for 'SUMIF' in the dialog box, select it, and click OK to open the Function Arguments window.
4. Select your cross-sheet ranges: Click inside the 'Range' box, navigate to your data sheet tab at the bottom, and highlight your criteria column. Repeat this process for the 'Sum_range' box.
5. Confirm and apply: Enter your criteria in the middle box and click OK. WPS will automatically format the cross-sheet formula for you.

100% compatible with Microsoft Excel formulas and .xlsx file formats.User-friendly Function Arguments dialog for building cross-sheet references without syntax errors.Completely free to use with a lightweight, fast installation package.

Frequently Asked Questions

Why is my cross-sheet SUMIF formula returning an error?

Errors often occur if the referenced sheet name contains spaces but isn't wrapped in single quotes (e.g., 'Data Sheet'!A1:A10). Ensure your syntax includes the single quotes if there are spaces. Also, verify that your criteria range and sum range are exactly the same size.

Can I use SUMIFS to check multiple criteria on another sheet?

Yes, you can use the SUMIFS function to apply multiple conditions across worksheets. Note that the syntax differs from SUMIF: in SUMIFS, the sum range comes first, followed by the first criteria range and condition, then subsequent ranges and conditions.

How do I reference a whole column in another sheet instead of a specific range?

You can reference an entire column by omitting the row numbers in your formula. For example, use =SUMIF(Sheet3!D:D, 4545, Sheet3!G:G) to sum the entire G column based on the values in the entire D column. This is useful for data sets that frequently grow in size.

Excel & Data Problems