Complete Guide to Excel Conditional Sums: SUMIF, SUMIFS, DSUM, and SUMPRODUCT
This guide covers Excel conditional sums in one place. It includes the differences between SUMIF and SUMIFS, date, text, and partial-match (wildcard) criteria, multiple criteria, absolute-value sums, and DSUM and SUMPRODUCT alternatives with practical examples.
Quick Fix: The 6 Most Common Formulas
- Single-criteria sum (SUMIF)
=SUMIF(A2:A100,">0",B2:B100)Criteria range A (sales status/sign), sum range B (amount). The criterion is greater than 0.
- Multiple-criteria sum (SUMIFS)
=SUMIFS($C:$C,$A:$A,"온라인",$B:$B,">="&DATE(2025,1,1),$B:$B,"<"&DATE(2025,2,1))A = channel, B = date, C = amount. Sums online sales for January 2025.
- Partial match (wildcard)
=SUMIFS(C:C,A:A,"*반팔*",B:B,"여름")Product name contains “short-sleeve” and season equals summer.
- Not equal to (exclude)
=SUMIFS(C:C,A:A,"<>취소")Sums rows where the value in column A is not “Canceled.”
- Absolute-value sum (ignore signs)
=SUMPRODUCT(ABS(D2:D100)) - Database-style sum (DSUM)
=DSUM($A$1:$F$100,"금액",$H$1:$I$3)Create a criteria table in H1:I3 (with matching field names) to handle complex criteria flexibly.
SUMIF vs. SUMIFS: Which Should You Use?
| Item | SUMIF | SUMIFS |
|---|---|---|
| Number of criteria | 1 | 2 or more |
| Sum range | Optional (if omitted, the criteria range is summed) | Fixed as the first argument |
| Dates/text/wildcards | Supported | Supported (especially useful for combinations of criteria) |
| Alternatives | SUMPRODUCT/DSUM | DSUM, SUMPRODUCT |
Practical Examples: Reproducible Sample
Sample data (simplified): A = channel, B = order date, C = amount, D = notes
Row | Channel | Order Date | Amount | Notes
----|---------|-------------|--------|----------
2 | Online | 2025-01-03 | 120 | Normal
3 | Store | 2025-01-07 | 80 | Canceled
4 | Online | 2025-01-20 | 60 | Exchange
5 | Online | 2025-02-05 | 50 | Normal
1) Online total for January 2025
=SUMIFS($C$2:$C$5,$A$2:$A$5,"온라인",$B$2:$B$5,">="&DATE(2025,1,1),$B$2:$B$5,"<"&DATE(2025,2,1))2) Sum excluding “Canceled”
=SUMIFS($C$2:$C$5,$D$2:$D$5,"<>취소")3) Notes containing a keyword (partial match)
=SUMIFS($C$2:$C$5,$D$2:$D$5,"*정상*")4) Absolute-value sum
=SUMPRODUCT(ABS($C$2:$C$5))Common Mistakes and Checklist
- Date comparisons: Instead of text such as "2025-01-01," use
DATE(year,month,day)in calculations. This prevents regional setting issues. - Not equal to: Use the format "
<>value" (for example,"<>취소"). Spaces may also be treated as values. - Partial matches: Use the
*keyword*wildcard format. - Performance: Excessive use of full-column references such as C:C can slow down calculations → use a fixed range such as
$C$2:$C$100000. - Signs/absolute values: If values must be converted before summing, combine with
ABS.
Troubleshooting
| Symptom | Cause | Solution |
|---|---|---|
| Result is 0 | Date/text type mismatch | Use DATE() or normalize with VALUE() |
| Slow calculation | Full-column references or duplicate calculations | Use precise fixed ranges; switch to DSUM if needed |
| Partial match fails | Wildcard omitted | Enter the criterion in the *keyword* format |
| Only negative values are included | Comparison sign is reversed | Check ">0"/"<0" again |
Learn More (Internal Links)
- Complete Guide to the SUMIF Function
- SUMIFS Multiple-Criteria Guide
- Advanced SUMPRODUCT Uses
- Using ABS (Absolute Value)
- Excel Functions: From Basics to Advanced Applications
Official Documentation (Authoritative Sources)
Conclusion
For Excel conditional sums, SUMIF and SUMIFS are the foundation. Supplement them with DSUM or SUMPRODUCT based on complexity or performance needs to solve most real-world cases quickly.