The SUMIF and SUMIFS formulas in Google Sheets are indispensable tools for performing conditional summations. They simplify complex calculations, enabling you to save time and focus on analyzing results. From inventory management to employee hours tracking, these formulas offer versatile solutions for real-world data challenges. In this blog, we’ll cover four practical use cases with examples to showcase the power of SUMIF and SUMIFS.

What Are SUMIF and SUMIFS?

• SUMIF: Calculates the sum of values based on a single condition.
• =SUMIF(range, criterion, [sum_range])
• SUMIFS: Calculates the sum of values based on multiple conditions.
• =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], …)

Why Use SUMIF and SUMIFS?

• Time-Saving: Automates complex summations, eliminating manual calculations.
• Accurate: Ensures consistent results with well-defined conditions.
• Dynamic: Automatically updates when data changes.
• Flexible: Handles multiple conditions for advanced analytics

Practical Applications of SUMIF and SUMIFS

1. Inventory Management: Calculate Total Stock by Category

Scenario: An inventory manager wants to calculate the total stock for a specific product category.

Formula

Formula breakdown:

$A$4:$A:

• The range of cells in column A (Category) that the formula evaluates against the condition in F5.
• The $ makes the reference absolute, meaning it won’t change when the formula is copied to other cells.

F5:

• The criterion or condition to match (e.g., “Electronics”).
• Dynamically changes based on the value in F5.

$C$4:$C:

• The sum_range, or the range of cells in column C (Stock) that the formula sums when the condition in F5 is met.
• The $ ensures this range remains constant when the formula is copied.

Check the formula in the spreadsheet: Click here

How it works:

• The formula looks at the value in F5 (e.g., “Electronics”).
• It searches the range $A$4:$A for matches.
• For each match, it adds the corresponding value from the range $C$4:$C.

Benefits:

• Quickly calculates stock totals for specific categories, making inventory tracking more efficient.

2. Expense Tracking: Calculate Monthly Travel Expenses

Scenario: A finance team wants to calculate monthly travel expenses based on specific dates, grouping the expenses by month dynamically.

Formula

Formula breakdown:

$C$4:$C:

• The sum_range, or the range of cells containing the amounts to sum.
• $ ensures this range stays fixed when copying the formula.

$A$4:$A:

• The criteria_range1, where the formula checks for the category (e.g., “Travel”).
• $ locks this range for reusability.

“Travel”:

• The criterion1 to filter rows where the category is “Travel.”

$B$4:$B:

• The criteria_range2, where the formula checks the dates.
• $ locks this range for consistency.

“>=”&$F5:

• The criterion2 specifies the start of the date range.
• $F5 contains the first day of the desired month (e.g., 01/01/2024 for January 2024).
• The “>=” ensures that only rows with dates on or after this start date are included.

“<=”&EOMONTH($F5,0):

• The criterion3 specifies the end of the date range.
• EOMONTH($F5,0) calculates the last day of the month based on the value in $F5.
• The “<=” ensures that only rows with dates on or before this end date are included.

Check the formula in the spreadsheet: Click here

How it works:

1. Category Match:
• Filters the data to include only rows where the category is “Travel.”
2. Date Range:
• Filters further to include only rows where the date falls between the start ($F5) and the end of the month (EOMONTH($F5,0)).
3. Summation:
• Adds up the values in the sum_range ($C$5:$C) that meet both conditions.

Benefits:

• Dynamic Date Filtering:
• Uses EOMONTH to dynamically determine the end of the month, ensuring accurate date ranges.
• Multi-Criteria Filtering:
• Combines category matching and date range filtering in a single formula for precise results.
• Scalable:
• Works seamlessly with large datasets, handling multiple months and categories efficiently.
• Reusable:
• With locked references ($), the formula can be easily copied across cells for different months.

3. Sales Performance: Calculate Sales for a Product in a Region

Scenario: A sales manager wants to calculate the total sales of “Product A” for each region dynamically.

Formula

Formula breakdown:

$C$4:$C:

• The sum_range, or the range containing the sales values to be summed.
• $ ensures the reference stays fixed when the formula is copied.

$A$4:$A:

• The criteria_range1, or the range containing product names.
• $ locks this range for consistency.

“Product A”:

• The criterion1, which filters rows where the product is “Product A.”

$B$4:$B:

• The criteria_range2, or the range containing region names.
• $ locks this range for reuse.

F5:

• The criterion2, dynamically referencing the region name (e.g., “North”) for each row.

Check the formula in the spreadsheet: Click here

How it works:

• The formula filters rows where Product = “Product A”.
• It further filters these rows by matching the region (e.g., “North”) specified in column F.
• The formula sums the sales values in column C for rows meeting both conditions.

Benefits:

• Dynamic Region Filtering: Automatically calculates totals for each region without manual filtering.
• Multi-Criteria Matching: Combines product and region conditions seamlessly.
• Scalability: Handles large datasets with multiple regions and products efficiently.
• Reusable Formula: Can be copied across rows for different regions dynamically.

4. Employee Hours: Calculate Total Hours by Project and Employee

Scenario: A project manager wants to calculate the total hours worked by specific employees on specific projects dynamically.

Formula breakdown:

$C$4:$C:

• The sum_range, or the range containing the hours to be summed.
• The $ locks this range so it doesn’t change when the formula is copied.

$A$4:$A:

• The criteria_range1, where the formula checks for matching employee names.
• The $ locks this range for consistency across all rows.

F5:

• The criterion1, dynamically referencing the employee name (e.g., “Alice”).
• This remains relative and adjusts when the formula is copied to other rows.

$B$4:$B:

• The criteria_range2, where the formula checks for matching project names.
• The $ locks this range to ensure it doesn’t shift when copied.

G5:

• The criterion2, dynamically referencing the project name (e.g., “Project X”).
• This remains relative and adjusts when the formula is copied to other rows.

Check the formula in the spreadsheet: Click here

How it works:

• Match Employee Name:
  • • Filters rows where the employee name in $A$4:$A matches the value in F5.
• Match Project Name:
  • • Further filters rows where the project name in $B$4:$B matches the value in G5.
• Sum Hours:
  • • Adds up the values in $C$4:$C for rows meeting both conditions.

Benefits:

• Dynamic Summations: Calculates hours dynamically for any employee-project combination.
• Multi-Criteria Filtering: Combines employee and project conditions in a single formula.
• Reusable: Can be applied across multiple rows for different employee-project combinations.
• Scalable: Efficiently handles large datasets with multiple employees and projects.

Simplify your workflows with Sumif and Sumifs

The SUMIF and SUMIFS formulas are invaluable for handling conditional summations in Google Sheets. Whether tracking inventory, analyzing sales, or monitoring employee hours, these formulas help you make informed decisions quickly and efficiently.

Have a unique use case for SUMIF or SUMIFS? Share it in the comments—we’d love to hear your ideas!