SUMIFS, COUNTIFS and Excel Text and Date Functions | Free Excel Course: From Beginner to Advanced

What you'll learn

Sum and count with a condition using SUMIF and COUNTIF.
Answer business questions with SUMIFS, COUNTIFS and AVERAGEIFS.
Build a summary report that updates itself.
Clean up text and calculate with dates.
Avoid the mistakes that make SUMIFS "come up short" without any warning.

📥 Practice this lesson

Summarize sales by region, month and category, and clean up customer names with TRIM and PROPER. Check: March sales should total $16,039.00 across 37 orders.

practice-excel.xlsx · Sales and Customers to Clean sheets

How much did the West region sell in March? How many orders are still pending? What's the average order size for one customer? You answer these questions with Excel's conditional summary functions: SUMIFS, COUNTIFS and AVERAGEIFS. In this lesson you'll also learn the text and date functions people use most to clean and prepare data.

3"IFS" functions to sum, count and average with conditions

127range/criteria pairs fit in a single SUMIFS formula

Ctrl + EFlash Fill: clean and split text without writing formulas

One condition
Several conditions
Criteria and dates
Summary report
Text and dates

COUNTIF and SUMIF: one condition

=COUNTIF(range, criteria)
=SUMIF(criteria_range, criteria, sum_range)

In a sales list where column B is the region and column D is the amount:

Number of West orders:   =COUNTIF(B:B, "West")
West sales amount:       =SUMIF(B:B, "West", D:D)

1. What does =SUMIF(B2:B100,"West",D2:D100) do?

Counts how many rows are from the West region
Adds the amounts in column D for rows whose region is West
Adds the entire column D

SUMIF takes the range to check, the criteria and the range to add.

The IFS versions: several conditions

In real work you almost always need more than one criterion. The "IFS" functions accept as many range/criteria pairs as you want, and every one must be true at the same time:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

Watch the argument order

In SUMIF the range to add goes last; in SUMIFS it goes first. It's the most common mix-up. If you always use SUMIFS (it works with a single condition too), the problem disappears.

Say you have a sales table with Date (A), Region (B), Salesperson (C), Amount (D) and Status (E):

Paid sales in the West:
=SUMIFS(D:D, B:B, "West", E:E, "Paid")

Megan's pending orders:
=COUNTIFS(C:C, "Megan", E:E, "Pending")

Average order in the West:
=AVERAGEIFS(D:D, B:B, "West")

Criteria with operators, wildcards and cells

You want…	Criteria
Amounts over 1,000	`">1000"`
Anything except "Canceled"	`"<>Canceled"`
Text that starts with "Dist"	`"Dist*"`
Text that contains "south"	`"south"`
Non-empty cells	`"<>"`
Greater than the value in H1	`">"&H1`
Equal to the value in G2	`G2`

Criteria aren't case-sensitive: "west" and "West" count the same.

Summing between two dates (one month's sales)

To add up March 2026 sales with the start date in H1 (3/1/2026) and the end date in H2 (3/31/2026):

=SUMIFS(D:D, A:A, ">="&H1, A:A, "<="&H2)

You use the same date column twice, once for each boundary. Change H1 and H2 and you get any period's total.

Try it on the practice file's Sales sheet (dates in A, region in B, amount in I) with H1 = 3/1/2026 and H2 = 3/31/2026:

March sales:          =SUMIFS(Sales!I:I, Sales!A:A, ">="&H1, Sales!A:A, "<="&H2)                       → $16,039.00
March sales, West:    =SUMIFS(Sales!I:I, Sales!A:A, ">="&H1, Sales!A:A, "<="&H2, Sales!B:B, "West")  → $5,261.50
March order count:    =COUNTIFS(Sales!A:A, ">="&H1, Sales!A:A, "<="&H2)                              → 37

Make sure the report ties out

Whenever you build a summary with these functions, add up the whole grid and compare it with =SUM(Sales!I:I). If they don't match, some rows don't fit any category, almost always because of an extra space or a misspelled region.

2. In SUMIFS, which argument comes first?

The range to add
The first criteria
The first criteria range

Unlike SUMIF, SUMIFS puts the sum range first, followed by the pairs of criteria range and criteria.

Build a summary report

The professional way to use these functions is a small summary grid: regions down the rows, statuses across the columns, and a single formula with mixed references.

	G	H	I
1		Paid	Pending
2	West	`=SUMIFS($D:$D, $B:$B, $G2, $E:$E, H$1)`
3	Central
4	East

Copy the formula in H2 across H2:I4 and the report is done. It's exactly what a PivotTable does, but with a fixed layout you can format any way you like.

Worked example: a coffee roaster's report in Portland, Oregon

The practice file's Sales sheet belongs to a specialty coffee roaster in Portland that sells wholesale to cafés and gift shops in three regions. There, Region is in B, Category in F and Amount in I. The sales manager wants a grid by region and category. On a new sheet, with regions in A2:A4 and categories in B1:E1, one formula in B2 copied across the grid does it:

=SUMIFS(Sales!$I:$I, Sales!$B:$B, $A2, Sales!$F:$F, B$1)

Region	Accessories	Chocolate	Coffee	Tea	Total
West	22,532.00	7,814.00	10,041.50	4,530.50	44,918.00
Central	15,642.00	8,406.00	11,089.00	4,437.50	39,574.50
East	18,201.00	8,296.00	8,374.50	3,395.50	38,267.00
Total	56,375.00	24,516.00	29,505.00	12,363.50	122,759.50

Sales by category (USD)

Accessories$56,375

Coffee$29,505

Chocolate$24,516

Tea$12,364

Two insights you'd never see scrolling through 300 rows. First, accessories (mugs and French presses) bring in more than coffee and chocolate combined ($56,375 vs. $54,021). Second, Central has the most orders (=COUNTIF(Sales!B:B,"Central") = 107) but the smallest average order (=AVERAGEIFS(Sales!I:I, Sales!B:B, "Central") = $369.86, vs. $449.18 in the West). Lots of small orders: a hint to offer bundles or a minimum order in that region.

Mistakes that make totals wrong without warning

Ranges of different sizes: SUMIFS(D2:D300, B2:B299, "West").

Every range must have the same number of rows, or Excel returns #VALUE!. Use whole columns or a table.

Typing ">=H1" with the cell inside the quotes.

Join the operator and the cell: ">="&H1. Inside quotes, H1 is just text.

Dates that include a time, with "<="&H2 for the last day.

Use "<"&H2+1: it includes the whole last day, whatever the time.

Data with trailing spaces ("West ") that doesn't match "West."

Clean it first with TRIM or Power Query. A total that won't tie out usually starts here.

3. The start date is in H1. Which criteria sums sales from that date onward?

">=H1"
"=>"&H1
">="&H1

The operator goes in quotes and is joined to the cell's value with &. With ">=H1" Excel compares against the text H1, and "=>" isn't a valid operator.

Text functions for cleaning data

Data from other systems, web forms or emails usually arrives "dirty." These functions get you out of trouble:

Function	What it does	Example → result
`TRIM`	Removes extra spaces	`=TRIM(" Sarah Wilson ")` → Sarah Wilson
`UPPER` / `LOWER` / `PROPER`	Changes capitalization	`=PROPER("SARAH WILSON")` → Sarah Wilson
`LEFT` / `RIGHT`	Takes characters from one end	`=LEFT("TX-2041",2)` → TX
`MID`	Takes characters from a position	`=MID("TX-2041",4,4)` → 2041
`LEN`	Counts characters	`=LEN("TX-2041")` → 7
`&` or `TEXTJOIN`	Joins text	`=B2&" "&C2` → Sarah Wilson
`TEXT`	Formats a number inside text	`="Total: "&TEXT(D2,"$#,##0.00")`
`SUBSTITUTE`	Replaces text	`=SUBSTITUTE("555-0142","-","")` → 5550142

Flash Fill (Ctrl + E)

If column A has full names and you want only the last names, type the first one by hand in B2 and press Ctrl + E (or Data → Flash Fill). Excel detects the pattern and fills in the rest without formulas. It works for splitting, joining and reformatting text, and it's been in Excel since 2013.

4. A cell contains " sarah WILSON " with extra spaces. Which formula returns "Sarah Wilson"?

=UPPER(A2)
=PROPER(TRIM(A2))
=CLEAN(A2)

TRIM removes the extra spaces and PROPER capitalizes the first letter of each word. CLEAN removes non-printing characters, not spaces.

Date functions you'll need

TODAY() and NOW()

Today's date (and time). =TODAY()-A2 gives the days elapsed since a date.

Use it for: how many days an invoice is past due.

YEAR, MONTH and DAY

YEAR(A2), MONTH(A2), DAY(A2) pull out parts of a date.

Use it for: helper columns to summarize by month.

EOMONTH and NETWORKDAYS

EOMONTH(A2,0): last day of the month (with 1, of the next month). NETWORKDAYS(start, end, [holidays]): business days, skipping weekends and the holidays you list.

Use it for: due dates and delivery windows around federal holidays.

DATEDIF

DATEDIF(birth_date, TODAY(), "y"): age in completed years. It doesn't appear in the function list, but it works.

Use it for: ages and years of service.

These functions combine nicely with SUMIFS. Type only the first day of a month in H1 (3/1/2026) and let EOMONTH find the last one: =SUMIFS(Sales!I:I, Sales!A:A, ">="&H1, Sales!A:A, "<="&EOMONTH(H1,0)) returns the same $16,039.00 for March. Change H1 to 5/1/2026 and you get May, the roaster's best month, without touching the formula.

Practice exercise

Using your expense list (Date, Description, Category, Amount), calculate:

How much you spent on the "Groceries" category.
How many expenses were over $50.
Total "Transportation" spending between two dates that you type in two cells.
A summary with one row per category and its total, using a single copied formula.

See solution

1) =SUMIFS(D:D, C:C, "Groceries")
2) =COUNTIFS(D:D, ">50")
3) =SUMIFS(D:D, C:C, "Transportation", A:A, ">="&H1, A:A, "<="&H2)
4) Categories in G2:G6 and in H2: =SUMIFS($D:$D, $C:$C, G2)  → copy down

For a list of categories without duplicates in Microsoft 365: =UNIQUE(C2:C100). If this list is your household budget, our guide on how to make a budget shows which categories are worth tracking.

Want more formulas like these in one place? Our guide to Excel formulas for data analysis covers 18 of them with worked examples.

Summary

SUMIFS, COUNTIFS and AVERAGEIFS answer almost any business question.
Criteria accept operators (">1000"), wildcards ("*south*") and cell references (">="&H1).
TRIM, PROPER and Ctrl + E clean data in seconds.
YEAR, MONTH, EOMONTH and NETWORKDAYS handle date math.
A summary grid with mixed references ($A2, B$1) fills in with a single formula.

Next lesson: Data Validation, Drop-Down Lists and Sheet Protection in Excel. You'll build drop-down lists (including dependent ones), formula-based rules and error alerts, and protect your sheets and workbooks.