SUMIFS & AVERAGEIFS: Conditional Sums and Averages (Dates, Text, OR, and Excluding Blanks)
Create multi-criteria sums and averages without PivotTables. These practical patterns also cover OR, NOT, and excluding blanks.
Syntax and Core Concepts
| Function | Syntax | Description |
|---|---|---|
| SUMIFS | =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2]…) | Conditional sum using AND logic |
| AVERAGEIFS | =AVERAGEIFS(avg_range, criteria_range1, criteria1, ...) | Conditional average using AND logic |
Advantages: Fast and readable; works well with arrays and spilled ranges.
Note: Every criteria range must have the same number of rows and columns as the sum or average range.
Date Ranges (Including Time Considerations)
Example: Total for Seoul keyboards in Q1 2025
=SUMIFS(Sales, Region,"Seoul", Category,"Keyboard",
Date, ">="&DATE(2025,1,1), Date, "<="&DATE(2025,3,31))Handling times (hours:minutes:seconds): Compare dates only with INT(Date), or set the end date to less than the following day, such as "<"&EOMONTH(start,2)+1.
Text, Wildcards, NOT, and Excluding Blanks
=SUMIFS(Sales, Seller, "Kim*", Brand, "<>""") // Starts with Kim; Brand is not blank
=AVERAGEIFS(Score, Dept, "<>Sales", Name, "*Lee*") // Excludes Sales; name contains LeeWildcards: * (zero or more characters), ? (one character), and ~ (escape character).
Three Ways to Use OR Criteria
① Add two calculations (simple and fast)
=SUMIFS(Sales, Region,"Seoul") + SUMIFS(Sales, Region,"Busan")② Aggregate with the dynamic array MAP function (category list in G2:G)
=SUM(MAP(G2:G5, LAMBDA(c, SUMIFS(Sales, Category, c))))③ Filter first, then sum at once
=SUM(FILTER(Sales, (Region="Seoul")+(Region="Busan")))12 Practical Patterns
① Monthly totals (spilled table)
=LET(m, EOMONTH(Date,0), u, UNIQUE(m), HSTACK(u, MAP(u, LAMBDA(mm, SUMIFS(Amount, m, mm)))))② Average excluding zeros
=AVERAGEIFS(Score, Score, ">0")③ Rolling total for the last 30 days
=SUMIFS(Sales, Date, ">="&TODAY()-30)④ Time-of-day filter (9:00 AM–6:00 PM)
=SUMIFS(Calls, Hour, ">="&TIME(9,0,0), Hour, "<="&TIME(18,0,0))⑤ Normalize text-formatted currency values
=SUMIFS(NUMBERVALUE(Amount), Currency,"USD")⑥ Average excluding blanks and #N/A
=AVERAGEIFS(Value, Value, "<>", Value, "<>#N/A")⑦ Sum of the top or bottom N categories
=SUM(TAKE(SORTBY(BYROW(UNIQUE(Category),LAMBDA(r,SUMIFS(Sales,Category,INDEX(UNIQUE(Category),ROW(r)) ))),, -1), -5))⑧ Pivot-style cross-tab by month and region
=LET(m, UNIQUE(EOMONTH(Date,0)), r, UNIQUE(Region),
MAKEARRAY(ROWS(r), ROWS(m), LAMBDA(i,j, SUMIFS(Sales,Region, INDEX(r,i), EOMONTH(Date,0), INDEX(m,j)))))⑨ Average lead time for projects with a status of “Done”
=AVERAGEIFS(LeadDays, Status, "Done")⑩ Total pay excluding bonuses
=SUMIFS(Pay, Type, "<>Bonus")⑪ Sales versus target by product (with a conditional average)
=SUMIFS(Sales, Product, K2) / AVERAGEIFS(Target, Product, K2)⑫ Detect missing categories (total = 0)
=FILTER(UNIQUE(Category), MAP(UNIQUE(Category), LAMBDA(c, SUMIFS(Sales, Category, c)))=0)Alternative for Older Versions (SUMPRODUCT)
=SUMPRODUCT( (Region="Seoul")*(Category="Keyboard")*(Date>=DATE(2025,1,1))*(Date<=DATE(2025,3,31)) * Sales )For an average, use the form SUMPRODUCT(criteria*values)/SUMPRODUCT(criteria).
Common Mistakes and Checks
- Mismatched range lengths → Use a table (CTRL+T) to standardize column references.
- Mixed dates and times → Use
INT(Date)or the “less than the following day” pattern. - Numbers stored as text → Normalize them with
NUMBERVALUE. - Expecting OR logic → SUMIFS uses AND logic. Handle OR with “addition/filtering.”
Boost Search Visibility: SEO Boost Pack (Applied to This Document)
- Title variation (for testing): “SUMIFS vs. AVERAGEIFS Complete Guide | Formulas for Dates, OR, and Excluding Blanks”.
- Keyword summary in the first paragraph: Includes “multi-criteria sums and averages, date ranges, OR, and excluding blanks” (for snippet optimization).
- FAQ schema for PAA coverage (including questions about dates, OR, and blanks).
- Internal link cluster:
- /excel-filter-sort-unique (combine with preprocessing and filtering)
- /excel-maxifs-minifs-conditional-extremes (compare extremes)
- /excel-textsplit-textbefore-textafter (aggregate after splitting keys)
- /excel-round-roundup-rounddown (report rounding policies)
- Example tables and code readability: Standardize short recipe-style examples (= longer time on page).
- Image ALT: “SUMIFS AVERAGEIFS conditional sums and averages — Kkong-i wearing glasses combines filters to calculate totals in an editor concept”.
- File naming convention:
sumifs-averageifs-tutorial-editor-kkongi-1280x720-v1.webp/...-og-1200x630-v1.jpg. - Improve CTR: A yellow summary box below the H1 (benefits and time promise) plus a list-style table of contents (jump links).
- E-E-A-T: Demonstrate expertise with “12 Practical Patterns” and clearly identify the “Notes/Policies” section.
Summary
| Goal | Primary Formula |
|---|---|
| Multi-criteria sum/average | SUMIFS / AVERAGEIFS |
| Date range | ">="&DATE(...) & "<="&DATE(...) |
| OR/NOT/excluding blanks | Addition · MAP · FILTER / "<>" / ">0" |
| Older versions | Use SUMPRODUCT as an alternative |