How to Use the SUMIFS Function in Excel

The SUMIFS function is one of the most powerful tools in Excel for analyzing data based on specific conditions. It allows you to total numbers only when certain criteria are met, such as technician name, system type, or job category. In this lesson, you’ll learn how SUMIFS works and see how it’s used to calculate HVAC service hours and revenue by system type and technician, helping transform raw work order data into meaningful performance metrics like revenue per hour.

Download the Excel file used in this tutorial:

Download

Using the SUMIFS Function in Excel

1. Start with a structured dataset

  • Use a dataset that includes fields such as technician name, system type, hours worked, and revenue.
  • Identify the numeric columns you want to summarize, such as hours or revenue.
  • These numeric fields will be the values the SUMIFS function aggregates.

2. Summarize hours by system type

  • Begin by calculating the total hours worked for each system type.
  • Use the SUMIFS function and select the hours column as the range to sum.
  • Use the system type column as the criteria range.
  • Reference the system type label in your summary table as the criteria.
  • Drag the formula down to calculate the total hours for each system category.

3. Summarize revenue by system type

  • Repeat the same process to calculate total revenue for each system type.
  • Use SUMIFS again, but this time select the revenue column as the range to sum.
  • Keep the system type column as the criteria range.
  • Reference the system type labels in the summary table so the results update automatically when copied down.

4. Format the revenue results

  • Format the revenue totals as currency.
  • Adjust decimal places if needed so the report is easier to read.
  • This makes it easier to compare system types visually.

5. Calculate dollars per hour by system type

  • Divide the revenue totals by the total hours.
  • This gives you the revenue per hour for each system category.
  • Copy the calculation down to complete the analysis for all system types.

6. Summarize hours by technician

  • Use SUMIFS again to calculate total hours worked by each technician.
  • Set the hours column as the value to sum.
  • Use the technician name column as the criteria range.
  • Reference the technician names in the summary table.
  • Drag the formula down to calculate totals for each technician.

7. Summarize revenue by technician

  • Repeat the same process using the revenue column as the range to sum.
  • Keep the technician column as the criteria range.
  • Copy the formula down to calculate revenue totals for all technicians.

8. Calculate dollars per hour by technician

  • Divide revenue totals by total hours for each technician.
  • This creates a quick productivity metric that compares technician performance.
  • Drag the formula down to complete the table.

9. Expand the analysis with multiple criteria

  • The power of SUMIFS comes from adding additional criteria.
  • You can combine conditions such as:
    • technician name
    • system type
    • job type
    • month
  • Each additional condition creates a more specific data segment, allowing you to break results into precise operational buckets.

10. Use SUMIFS to build operational reporting

  • By combining different criteria, you can calculate metrics like:
    • revenue by technician and system type
    • hours by technician and month
    • revenue by technician, system type, and time period
  • This allows you to transform raw datasets into structured performance reports that reveal operational insights.

The SUMIFS Function in Excel

Q1. What does the SUMIFS function do in Excel?
 The SUMIFS function adds numbers from a range only when one or more conditions are satisfied. For example, it can calculate total revenue for a specific technician, system type, or date range within a dataset.

Q2. What is the difference between SUMIF and SUMIFS?
 The SUMIF function allows you to sum values based on a single condition, while SUMIFS allows multiple conditions. For example, you can calculate revenue for a specific technician AND a specific system type at the same time.

Q3. Why is SUMIFS useful for business analysis?
 SUMIFS allows you to break large datasets into meaningful groups. This makes it possible to analyze performance by employee, product category, service type, location, or time period, helping turn raw data into actionable insights.

Q4. Can SUMIFS work with more than two criteria?
 Yes. One of the strengths of SUMIFS is that it supports multiple criteria pairs, allowing you to add as many conditions as needed to refine your analysis.

Q5. What types of data can SUMIFS calculate?
 SUMIFS can work with any numerical data such as revenue, hours worked, sales volume, costs, or inventory values. The function simply totals the values that match the specified conditions.

Q6. How does SUMIFS help build dashboards or reports?
 SUMIFS is commonly used to populate summary tables in dashboards. By grouping totals by categories like technician or system type, it becomes easier to calculate metrics such as revenue per hour, productivity, or sales by product type.

Get more lessons like this
by joining our newsletter

Receive weekly Excel tutorials, tips, and KPIs straight to your inbox

Join Now

Menu

Databoards

Analysis & Development

© 2025 All Rights Reserved.

Facebook Twitter Youtube