Function Problems

How to Use Excel SUMIF Formula for Values on Another Sheet

Huma Ashraf Ch Oct 10, 2026 872 views

Question details

The user needs to calculate a conditional sum using values located on a different worksheet, specifically matching a selected month.

How to Use Excel SUMIF Formula for Values on Another Sheet

Product: Excel
Device & OS: not provided
Scenario: Summarizing monthly data where the raw data and criteria ranges are stored on a separate worksheet within the same workbook.
Observed behavior: The user requires the correct syntax and method to reference cross-sheet arrays using the SUMIF or SUMIFS functions.

Before you start

Ensure both your destination sheet and source data sheet are open in the same workbook, and verify that the column containing the criteria (like months) is formatted consistently.

Solution 1Recommended

Use the SUMIF Function for a Single Condition Across Sheets

Apply the SUMIF function to add up values from a different worksheet when you only have one criterion, such as filtering data by a specific month.

The SUMIF function is perfect when you need to match exactly one condition. When referencing another sheet, you simply prefix the cell range with the sheet name followed by an exclamation mark (e.g., Sheet2!).

Select the Destination Cell

Click on the cell in your destination worksheet where you want the final sum result to be displayed.

Start the SUMIF Formula

Type =SUMIF( into the cell or the formula bar to begin the function.

Select the Criteria Range on the Source Sheet

Navigate to the sheet containing your source data (e.g., Sheet2). Click and select the entire column that holds your months (e.g., Sheet2!A:A). Add a comma to separate the argument.

Enter the Criterion

Type your condition enclosed in quotation marks, such as "September", or click a cell containing the month name. Add another comma.

Select the Sum Range and Finish

Select the column on the source sheet that contains the numeric values to be summed (e.g., Sheet2!B:B). Close the parenthesis by typing ) and press Enter to complete the formula.

Use the SUMIF Function for a Single Condition Across Sheets

Formula Example: Your final formula should look like this: =SUMIF(Sheet2!A:A, "September", Sheet2!B:B)

Solution 2

Use the SUMIFS Function for Multiple Conditions

Use SUMIFS if you need to sum values from another sheet based on multiple criteria, like a specific month and a specific product category.

Effortless Data Analysis

Use WPS Spreadsheet to Easily Apply Cross-Sheet Formulas

WPS Spreadsheet provides seamless support for advanced functions like SUMIF and SUMIFS. With its intuitive interface, automatic formula hints, and robust cross-sheet referencing, summarizing large datasets is incredibly easy and efficient.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook containing your source data and summary sheets.
2. Enter the Formula: Click the target cell, type =SUMIF(, and use your mouse to seamlessly click across different worksheet tabs to select your ranges.
3. Follow the On-Screen Prompts: Use the floating formula tooltips provided by WPS to easily input the correct arguments in order, then press Enter to instantly calculate the results.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Intuitive formula prompts and auto-completion to reduce syntax and typing errors.Fast processing engine designed for large datasets with complex cross-sheet references.Free and lightweight alternative for everyday data analysis.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIF formula returning zero when referencing another sheet?

This usually happens if the criteria text doesn't exactly match the data source (for example, hidden trailing spaces in the cells) or if the data ranges differ in size. Ensure ranges like Sheet2!A:A and Sheet2!B:B are properly aligned.

Can I reference a cell instead of typing the month name in quotes?

Yes. Instead of typing "September" manually, you can click on a cell containing the word September (e.g., cell C1 on your current sheet). The formula would simply look like =SUMIF(Sheet2!A:A, C1, Sheet2!B:B).

Does the SUMIF formula update automatically if the data on the other sheet changes?

Yes, as long as your workbook's calculation option is set to Automatic. Any edits, additions, or deletions made in the source sheet will instantly recalculate and update the sum in your destination sheet.

Excel & Data Problems