COUNTIF, SUMIF & SUMIFS

SUM adds everything in a range. Reports usually need something narrower: sales by one person, the number of orders above ₹2,000, the total for the first half of the month. The conditional functions COUNTIF, SUMIF, COUNTIFS and SUMIFS count and add only the rows that match.

In this lesson you will use all four functions on a shop's sales register, write criteria for text, numbers and dates, use wildcards, and build a small summary table that updates itself.

The sample sheet

A mobile accessories shop in Lucknow has three branches. Here are its October 2026 sales:

The total of column E is ₹15,990.

COUNTIF: count matching cells

=COUNTIF(range, criteria)

How many sales did Priya make?

=COUNTIF(B2:B11, "Priya")

4

The criteria is not case-sensitive, so "priya" works too. For numbers, put the comparison and the number together in quotes. How many sales were ₹2,000 or more?

=COUNTIF(E2:E11, ">=2000")

3

SUMIF: add matching rows

=SUMIF(range, criteria, [sum_range])

SUMIF checks the range and adds the matching cells of sum_range. Priya's total sales:

=SUMIF(B2:B11, "Priya", E2:E11)

4896

Excel looked down column B, found Priya in rows 2, 4, 7 and 11, and added 1299 + 399 + 1899 + 1299 from column E. If the condition is on the numbers themselves, leave out sum_range: =SUMIF(E2:E11, ">1500") adds every sale above ₹1,500 and returns 11295.

COUNTIFS and SUMIFS: several conditions

The plural versions accept many condition pairs, and a row is counted only when all conditions match. Priya's sales at the Hazratganj branch only:

=SUMIFS(E2:E11, B2:B11, "Priya", C2:C11, "Hazratganj")

2997

Her Gomti Nagar sale of ₹1,899 is left out. COUNTIFS works the same way without a sum range: =COUNTIFS(D2:D11, "Earbuds", C2:C11, "Aliganj") returns 1.

Criteria from a cell

Typing "Priya" into the formula means editing it every time. Put the criteria in a cell instead. With a salesperson's name in H2:

=SUMIF(B2:B11, H2, E2:E11)

When H2 says Farhan, the result is 6297. To combine an operator with a cell, join them with &. With a minimum amount in H3:

=COUNTIF(E2:E11, ">="&H3)

Writing ">=H3" instead does not work: Excel would compare the amounts with the text "H3".

Date criteria

Dates are numbers in Excel, so date criteria use the same operators. The sales from 1 to 15 October:

=SUMIFS(E2:E11, A2:A11, ">="&DATE(2026,10,1), A2:A11, "<="&DATE(2026,10,15))

10293

Using DATE avoids any confusion between day-month and month-day settings. The column is listed twice because there are two conditions on it: on or after the 1st, and on or before the 15th.

Wildcards for partial matches

Two wildcard characters work in text criteria:

For example, =COUNTIF(D2:D11, "*bank*") returns 2. To search for a real asterisk or question mark, put a tilde before it: "~*".

Related functions

MAXIFS and MINIFS need Excel 2019 or later.

A summary table that updates itself

Put the names in G2:G4 and one formula in H2, copied down. The ranges use $ so they stay fixed while the name reference moves:

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

Column I uses =COUNTIF($B$2:$B$11, G2). The three totals add up to 15990, the same as the whole column: a quick check that nobody was missed. Add a new sale inside the ranges and the summary updates. In Excel Tables you will make the ranges grow automatically too, and PivotTables build summaries like this with a few clicks.

Advanced: OR conditions

SUMIFS combines conditions with AND. For "Charger or Cover", give a list of criteria in curly braces and add the results:

=SUM(SUMIFS(E2:E11, D2:D11, {"Charger","Cover"}))

4695

SUMIFS returns two totals, 3897 for chargers and 798 for covers, and SUM adds them.

Common mistakes

• Mixed-up argument order between SUMIF and SUMIFS. Check the tooltip as you type.
• Ranges of different sizes. SUMIFS(E2:E11, B2:B10, ...) returns #VALUE!. All ranges must have the same number of rows.
• Operator and cell inside the quotes. Write ">="&H3, not ">=H3".
• Extra spaces in data. "Priya " with a trailing space does not match "Priya". Clean data with TRIM (see Text Functions).
• Ranges that move when copied. Lock data ranges with $ in summary tables.

Key points

• COUNTIF and SUMIF count or add cells that meet one condition.
• COUNTIFS and SUMIFS accept many conditions, all of which must match.
• In SUMIF the sum range is last; in SUMIFS it is first.
• Write comparisons in quotes (">=2000") and join operators to cells with & (">="&H3).
• Use DATE for date criteria and * or ? wildcards for partial text matches.

=SUMIF(C2:C11, "Hazratganj", E2:E11)
=COUNTIFS(B2:B11, "Farhan", D2:D11, "Earbuds")
=SUMIF(A2:A11, ">="&H5, E2:E11)
=SUMIF(D2:D11, "P*", E2:E11)

5496
2
5697
3798