Excel · Functions
Conditional Sums with SUMIF and SUMIFS? Master It in 1 Minute!
Learn how to calculate single- and multiple-criteria sums with one formula—no filters or PivotTables required. Master it easily with practical examples.
Why SUMIF and SUMIFS?
When you need a conditional sum, such as “sales team revenue only” or “this month’s sales only,” filtering and totaling the data each time can be time-consuming. With SUMIF (one criterion) and SUMIFS (multiple criteria), you can reduce repetitive work to less than half the time with a single formula.
Method 1) SUMIF – Single Criterion
Sample Data
| Name | Department | Sales Amount |
|---|---|---|
| Kim Cheol-su | Sales | 50000 |
| Lee Young-hee | Marketing | 60000 |
| Kim Cheol-su | Sales | 70000 |
| Park Min-su | Sales | 40000 |
Goal: Total sales amount for the “Sales” department
=SUMIF(B2:B5, "Sales", C2:C5)- Syntax: SUMIF(range, criteria, sum_range)
- Tip: Use a cell reference for the criterion to make maintenance easier:
=SUMIF(B2:B5, E1, C2:C5)
Method 2) SUMIFS – Multiple Criteria
Goal: Total sales amount for “Kim Cheol-su” in the “Sales” department
=SUMIFS(C2:C5, B2:B5, "Sales", A2:A5, "Kim Cheol-su")- Syntax: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
- Note: SUMIFS uses the sum range as its first argument.
Comparison Table – SUMIF vs. SUMIFS
| Function | Number of Criteria | Recommended Use |
|---|---|---|
| SUMIF | 1 | Simple conditional sums |
| SUMIFS | 2 or more | Sums filtered by multiple criteria |
3 Practical Tips
① Date Criteria
=SUMIF(C2:C10, ">"&DATE(2025,8,1))Totals only sales amounts after 2025-08-01.
② OR Criteria
=SUMIF(B2:B10,"Sales",C2:C10) + SUMIF(B2:B10,"Marketing",C2:C10)Totals sales amounts for the Sales or Marketing department.
③ Wildcards
=SUMIF(A2:A10, "Kim*", C2:C10)Totals only sales amounts for names that begin with “Kim.”
Summary
| Key Point | Practical Checklist |
|---|---|
| Use SUMIF and SUMIFS for single- and multiple-criteria sums | Specify the criteria and sum ranges correctly |
| Add separate sums for OR criteria | Implement by adding the results of two functions |
| Handle date and text patterns | Use DATE(), TODAY(), and wildcards |
FAQ
What is the difference between SUMIF and SUMIFS?
SUMIF handles one criterion, while SUMIFS handles multiple criteria.
How do I implement OR criteria?
Because SUMIFS uses AND criteria by default, add the results of two SUMIF or SUMIFS formulas to create OR criteria.
Can I use wildcards?
Yes. Use * and ?.