How to Use SUMIFS Formula to Sum Onsite and Offsite Values in Excel
Question details
The user needs to sum specific numerical values based on text criteria (such as "onsite" or "offsite") within a given column range.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Managing logs, attendance, or expenses where records are categorized by a specific location status, requiring dynamic calculation of totals for each category.
- Observed behavior
- To achieve the goal, separate totals must be calculated dynamically for each specific category using a conditional sum formula.
Ensure your dataset has clear, distinct columns for the location criteria (e.g., onsite or offsite) and the numerical values (e.g., duration or amounts) you want to sum.
Using the SUMIFS Function
Apply the SUMIFS formula to sum durations or amounts based on exact text matches in your location column.
The SUMIFS function is designed to add all arguments that meet multiple criteria. Even with a single criterion like 'onsite', SUMIFS is highly recommended for its flexibility if you need to add more conditions later. The basic syntax is =SUMIFS(sum_range, criteria_range1, criteria1).
Determine the columns containing your data. For example, assume column C (cells C2:C30) contains the location status and column F (cells F2:F30) contains the numerical duration or amount.
Select an empty cell where you want the 'Onsite' total to appear. Enter the formula =SUMIFS(F2:F30, C2:C30, "onsite") and press Enter.
In another empty cell designated for the 'Offsite' total, enter the formula =SUMIFS(F2:F30, C2:C30, "offsite") and press Enter.
Use WPS Spreadsheet to Manage Your Data Efficiently
WPS Spreadsheet fully supports the SUMIFS function and hundreds of other formulas, allowing you to seamlessly handle Excel datasets, calculate totals, and analyze onsite/offsite logs without any cost.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open the workbook containing your onsite and offsite records.
- 2. Initiate the Formula: Click on the destination cell where you want the total and type =SUMIFS( to bring up the intelligent formula tooltip.
- 3. Select Ranges and Criteria: Following the prompt, select your sum range (e.g., F2:F30), type a comma, select your criteria range (e.g., C2:C30), type another comma, and input "onsite".
- 4. Complete the Calculation: Press Enter to execute the formula and instantly view your aggregated data.
Frequently Asked Questions
Why is my SUMIFS formula returning 0 instead of the correct total?
This usually happens if the text in your criteria range contains hidden spaces (e.g., 'onsite ' instead of 'onsite') or if the sum range is formatted as text rather than numerical values. Use the TRIM function on your text column to remove extra spaces, and ensure your amount column is formatted as Numbers.
Can I use cell references instead of typing "onsite" directly into the formula?
Yes, you can type 'onsite' in a separate cell (for example, H2) and write your formula as =SUMIFS(F2:F30, C2:C30, H2). This makes your formula dynamic, automatically updating the total if you change the category text in cell H2 later.
What is the difference between SUMIF and SUMIFS?
SUMIF allows only one condition, and its syntax places the sum range at the end of the formula. SUMIFS supports one or multiple conditions, and the sum range is always the first argument. It is generally best practice to use SUMIFS because it makes adding future criteria much easier.
