How to Use SUMIF and COUNTIF in Excel

Applies to: Microsoft 365 (Excel for Windows, Excel for Mac, Excel for the Web); Excel 2016, 2019, 2021
Article Type: How-To
Last Updated: 2026-07-29

Summary

COUNTIF counts the rows that match a condition and SUMIF adds up their values, which is how you get "how much did Marketing spend" out of a 400-row expense log without sorting, filtering, or building a PivotTable. This article covers both functions, the criteria syntax that trips almost everyone on their first attempt, and the SUMIFS and COUNTIFS versions that handle several conditions at once.

Prerequisites

• Any version of Excel. SUMIF and COUNTIF have been standard for decades; SUMIFS and COUNTIFS work in Excel 2007 and later.
• Data laid out as a list with one record per row and a header row at the top.
• No admin rights required.

Instructions

Every example below uses the same sheet, a tab named Expenses, with data running from row 2 to row 200:

Row	A	B	C	D	E
1	Date	Department	Vendor	Category	Amount
2	02/07/2026	Marketing	Northwind Print	Printing	1240.00
3	03/07/2026	Finance	Contoso Software	Software	3600.00
4	06/07/2026	Marketing	Fabrikam Media	Advertising	8150.00
5	09/07/2026	Operations	Northwind Print	Printing	415.00
6	11/07/2026	Finance	Adatum Travel	Travel	2075.00

1. Count Matching Rows with COUNTIF

COUNTIF answers how many rows in a column match a value.

=COUNTIF(range, criteria)

1. Open the Expenses tab and click an empty cell — H2 works.
2. Type the formula:

   =COUNTIF(B2:B200, "Marketing")

3. Press Enter. The cell shows the number of rows where column B reads Marketing.
4. Lock the range so you can copy the formula to other cells. Click into the formula bar, click inside B2:B200, and press F4. It becomes $B$2:$B$200. On a Mac, press Command + T if F4 controls screen brightness.

Argument	What it means
range	The cells to look through. One column or one row — not a block.
criteria	What counts as a match. Text in quotes, a number, a comparison in quotes, or a cell reference.

Note: COUNTIF ignores capitalisation, so "marketing" and "MARKETING" return the same count as "Marketing". It also matches the whole cell, not part of it — "Print" does not match a cell reading Northwind Print unless you add a wildcard, covered in Step 4.

2. Total Matching Rows with SUMIF

SUMIF adds up the numbers on every row whose label matches your condition.

=SUMIF(range, criteria, sum_range)

1. Click an empty cell — H3.
2. Type the formula:

   =SUMIF(B2:B200, "Marketing", E2:E200)

3. Press Enter. The cell shows the total of column E for every row where column B reads Marketing — 9,390.00 from the sample rows above.

The argument order is the part people get wrong. The range you test comes first; the range you add comes last:

Argument	What it means	Required
range	The column holding the values to check against your criteria — the departments.	Yes
criteria	The condition, such as "Marketing" or ">1000".	Yes
sum_range	The column holding the numbers to add — the amounts.	No

Leave sum_range out and Excel adds up range itself. That is exactly what you want when the test and the total are the same column:

=SUMIF(E2:E200, ">1000")

That totals every amount over 1,000, with no second range needed.

Tip: Once you have a departmental total, dividing it by the grand total gives each department's share of spend. Lock the grand total with an absolute reference so the formula survives being copied down — How to Calculate Percentages in Excel covers that pattern along with the formatting rule that makes a correct result display as 25% rather than 0.25.

Warning: range and sum_range must be the same height and start on the same row. Pairing B2:B200 with E1:E199 returns a total that looks reasonable and is wrong by one row throughout, with no error to warn you. The safest habit is to select both ranges the same way every time — same start row, same end row.

3. Write the Criteria Correctly

The criteria argument follows rules that look arbitrary until you know them.

The one rule that explains the rest: anything other than a bare number or a plain cell reference goes inside one pair of double quotes, operator included.

What you want to match	Write it as	Notes
A specific word	"Marketing"	Quotes required. Capitalisation ignored.
A specific number	1000	No quotes needed. "1000" also works.
More than a number	">1000"	The > goes inside the quotes.
At most a number	"<=500"	Same rule for <, >=, <=.
Not a value	"<>Marketing"	<> means "not equal to".
Blank cells	""	Two quote marks, nothing between.
Any non-blank cell	"<>"	Reads as "not equal to nothing".

The two mistakes that produce most of the failed formulas:

• =SUMIF(E2:E200, >1000) — the operator is outside the quotes. Excel rejects the formula outright.
• =SUMIF(E2:E200, ">"1000) — the number is outside the quotes. Same result.

Both need the whole comparison in one string: ">1000".

Tip: A criteria string never contains a space around the operator. "> 1000" works in some Excel versions and silently returns zero in others. Write ">1000" with no space and it behaves the same everywhere.

4. Match Part of a Value with Wildcards

Use an asterisk or a question mark to match vendors and codes that only partly match.

Wildcard	Matches	Example
*	Any number of characters, including none	"North*" matches Northwind Print
?	Exactly one character	"KB-118?" matches KB-1180 and KB-1189
~	Cancels the wildcard that follows it	"~*" finds a literal asterisk

1. Click an empty cell.
2. Count every row whose vendor name starts with North:

   =COUNTIF(C2:C200, "North*")

3. Press Enter. From the sample rows, this returns 2.
4. Total every row whose vendor name contains Print anywhere:

   =SUMIF(C2:C200, "*Print*", E2:E200)

5. Press Enter. The result covers both Northwind Print rows — 1,655.00.

Note: Wildcards work on text only. Against a column of numbers or real dates, "*" matches nothing, because Excel stores those values as numbers rather than characters. For numbers, use a comparison such as ">1000"; for dates, use the two-condition method in Step 7.

5. Point the Criteria at a Cell

Reference a cell so your report updates when someone changes the filter instead of the formula.

Hard-coding "Marketing" into twelve formulas means editing twelve formulas to see the Finance numbers. Put the department name in a cell and point the formulas at it.

1. Click cell G2 and type Marketing. This is now your filter cell.
2. Click cell H2 and type:

   =SUMIF($B$2:$B$200, G2, $E$2:$E$200)

3. Press Enter. The total for Marketing appears.
4. Change G2 to Finance. The total in H2 updates the moment you press Enter.

A plain reference such as G2 needs no quotes and no extra syntax. Combining a cell with an operator or a wildcard is where the ampersand comes in — the & joins two pieces of text into one criteria string:

• Comparison against a cell. Put the operator in quotes, then &, then the reference:

  =SUMIF($E$2:$E$200, ">"&G3)

  Writing ">G3" instead searches for the literal characters >G3 and returns zero. This is the single most common cell-reference mistake in SUMIF.
• Wildcard around a cell. Wrap the reference in asterisks the same way:

  =COUNTIF($C$2:$C$200, "*"&G4&"*")

  With Print in G4, that counts every vendor whose name contains the word.

Verification: Type a value into your filter cell that you know appears exactly twice — a specific vendor, for instance — and confirm the COUNTIF returns 2. If it returns 0, the criteria is being read as literal text rather than joined; check that every & is present and that the operator sits inside its own quote marks.

6. Filter on Several Conditions with SUMIFS and COUNTIFS

SUMIFS and COUNTIFS take multiple condition pairs and match rows that satisfy all of them.

=SUMIFS(sum_range, range1, criteria1, range2, criteria2)

=COUNTIFS(range1, criteria1, range2, criteria2)

Notice what changed: in SUMIFS, the range you add comes first, not last. That reversal against SUMIF catches people out constantly.

1. Click an empty cell.
2. Total the Marketing spend on Printing only:

   =SUMIFS($E$2:$E$200, $B$2:$B$200, "Marketing", $D$2:$D$200, "Printing")

3. Press Enter. From the sample rows, that returns 1,240.00 — the Northwind Print row for Marketing, but not the Operations one.
4. Count the Marketing invoices over 1,000:

   =COUNTIFS($B$2:$B$200, "Marketing", $E$2:$E$200, ">1000")

5. Press Enter. The result is 2.

The rules that apply to both:

• Conditions are combined with AND, never OR. A row has to satisfy every pair to be included. To total two departments, add two SUMIFS formulas together.
• Every range must be the same size. Mixing B2:B200 with E2:E150 returns #VALUE!.
• Criteria syntax is identical to Step 3, including wildcards and the & joining pattern.
• You can use up to 127 condition pairs, which is far more than any readable report needs.

7. Total Between Two Dates

Combine two date conditions to build a report that covers a specific period.

A date range is two conditions on the same column: on or after the start, and on or before the end.

1. Click cell G6 and type the start date, 01/07/2026. Click G7 and type the end date, 31/07/2026. Use the date format your copy of Excel displays in column A.
2. Click cell H6 and type:

   =SUMIFS($E$2:$E$200, $A$2:$A$200, ">="&G6, $A$2:$A$200, "<="&G7)

3. Press Enter. The cell shows the total spend for July.
4. Change either date cell and press Enter. The total recalculates for the new period.

Note that column A appears twice — once for each condition. That is correct and expected.

Tip: Reference cells for the dates rather than typing them into the formula. A date written straight into the criteria, such as ">=01/07/2026", is interpreted using the regional date settings of whoever opens the file, so a workbook that totals July correctly on your machine can total January on a colleague's. Cell references sidestep the problem entirely.

Troubleshooting

TIP

SUMIFS and COUNTIFS handle a single condition perfectly well. Standardise on them for everything and you never have to remember which function puts the sum range first — it is always SUMIFS, always first. The only cost is one extra letter, and adding a second condition later becomes a matter of typing two more arguments rather than rewriting the formula.

Symptom / Error	Potential Cause	Solution
Returns 0 with matching rows visible	Trailing spaces, or numbers stored as text	Type =B2="Marketing" in an empty cell. A result of FALSE confirms the values differ. Use Find and Replace to strip spaces, or click the green triangle on the cells and choose Convert to Number.
#VALUE!	Ranges are different sizes, or point at a closed workbook	Make every range cover the same rows. SUMIF and COUNTIF cannot read a closed file — open the source workbook.
#NAME?	Function name misspelled, or a quote mark missing	Check the spelling and confirm each text criteria has an opening and a closing quote.
SUMIF totals the wrong column	Argument order confused with SUMIFS	SUMIF puts the range to add last; SUMIFS puts it first. See Steps 2 and 6.
A comparison criteria returns 0	Operator outside the quotes, or & missing before a cell reference	Write ">1000" as one string, and ">"&G3 when the number lives in a cell. See Steps 3 and 5.
Results change after copying the formula	Ranges not locked	Click into the formula bar, select each range, and press F4 to add the dollar signs.
Wildcards match nothing	The column holds numbers or dates, not text	* and ? only apply to text. Use a comparison for numbers and the date-range pattern in Step 7.

Related Articles

• How to Calculate Percentages in Excel
• How to Use IF and IFS Functions in Excel
• How to Use VLOOKUP in Excel
• How to Create a PivotTable in Excel
• How to Sort and Filter Data in Excel
• How to Fix Common Excel Formula Errors
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?