How to Use SUMIF and SUMIFS in Google Sheets (With Practical Examples)

Published on | ProSheetHub

Calculating overall totals in Google Sheets using =SUM() is straightforward, but real-world accounting, sales tracking, and project budgeting almost always demand conditional totals. For example, you may need to add up sales only for a specific sales rep, sum costs within a particular date window, or calculate inventory totals across multiple categories.

Google Sheets provides two functions to solve this: SUMIF (for single-condition totals) and SUMIFS (for multi-condition criteria). In this guide, you will learn the syntax, key differences, and practical formula examples for both.

1. The SUMIF Function: Single Criteria Sums

Use SUMIF when your calculation depends on a single rule (e.g., sum amounts where the department is "Marketing").

Formula Syntax

=SUMIF(criteria_range, criterion, [sum_range])
  • criteria_range: The column you want to evaluate against your condition (e.g., A2:A50).
  • criterion: The test condition, text, or number (e.g., "Marketing" or ">500").
  • sum_range: The column containing the actual numbers to add together (e.g., C2:C50). If omitted, Sheets sums the criteria_range directly.

Practical Example: Sum by Text Match

If Column A contains Department names and Column C contains Expenses, calculate the total spend for "Marketing" using:

=SUMIF(A2:A50, "Marketing", C2:C50)

You can also reference a cell instead of hardcoding the text:

=SUMIF(A2:A50, E1, C2:C50)

2. The SUMIFS Function: Multiple Criteria Sums

When you have two or more conditions that must be met simultaneously, switch to SUMIFS.

Critical Syntax Warning: In SUMIFS, the sum_range comes FIRST. In SUMIF, it comes last. Swapping this order is the most common reason for formula errors.

Formula Syntax

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Practical Example: Multiple Conditions

Suppose you want to sum sales in Column D where:

  1. The Region in Column A is "North"
  2. The Status in Column B is "Shipped"
=SUMIFS(D2:D100, A2:A100, "North", B2:B100, "Shipped")

3. Advanced SUMIFS: Date Ranges & Numeric Comparisons

A frequent task in financial reporting is summing values between two specific dates.

Summing Between Two Dates

To calculate sales in Column C occurring between January 1, 2026, and March 31, 2026, with dates listed in Column A:

=SUMIFS(C2:C100, A2:A100, ">=2026-01-01", A2:A100, "<=2026-03-31")

Dynamic Date Cell References

If your start date is in cell E1 and end date is in cell E2, concatenate the operator with an ampersand (&):

=SUMIFS(C2:C100, A2:A100, ">="&E1, A2:A100, "<="&E2)

4. SUMIF vs SUMIFS Quick Comparison

Feature SUMIF SUMIFS
Number of Conditions Exactly 1 1 to 127 conditions
Sum Range Position Last argument: (range, test, sum_range) First argument: (sum_range, range1, test1...)
Best Used For Quick category summaries Multi-layered reporting and date filters

Frequently Asked Questions

Why is my SUMIFS formula returning 0?

Double-check your quotation marks. If you reference a cell with a comparison operator, write ">"&F1 rather than ">F1". Also ensure the size of your sum_range matches all criteria_range boundaries exactly (e.g., both must span row 2 to 100).

Can I use wildcards with SUMIF?

Yes. You can use an asterisk (*) to match any sequence of characters. For example, =SUMIF(A2:A, "Tech*", B2:B) will sum values for "Technology", "Technical", or "Tech Support".

Recommended Next Steps

Organizing data totals makes building reports and dashboards straightforward. To expand your spreadsheet automation, explore these related guides:

  • Analyze Growth: Once your monthly category sums are calculated, measure your month-over-month performance with our guide on how to calculate percentage increase and decrease in Google Sheets.
  • Automate Billing: Combine category totals directly into customer receipts using our tutorial on building an automated invoice generator in Google Sheets.
  • Lookup Matching Rows: Need to return specific records rather than a calculated sum? Read our guide on XLOOKUP vs VLOOKUP in Google Sheets.
  • Array Calculations: Apply calculations dynamically across entire columns without dragging formulas using our guide on How to Use ARRAYFORMULA in Google Sheets.

Comments

Post a Comment