Skip to content

Excel function · Math

Excel SUMIFS function

SUMIFS adds up the amounts of the rows that meet all of your conditions together: cable sales in the West region, October revenue, one rep's invoices above 10,000.

In Turkish Excel: ÇOKETOPLA

What it does

In SUMIFS the range to add comes first, followed by pairs of criteria range and criteria. A row is added only if it meets every condition: =SUMIFS(D2:D50,B2:B50,"West",C2:C50,"Cable") adds only rows whose region is West and whose product is Cable. Up to 127 condition pairs are allowed.

A date range is built by giving the same date column twice: ">="&DATE(2025,10,1) for the start and "<"&DATE(2025,11,1) for the end. The criteria work as in SUMIF: comparison operators go in quotes, the * and ? wildcards are allowed, and case is ignored.

If any one of several conditions should be enough (OR), add two SUMIFS together or use SUMPRODUCT. To count with the same conditions use COUNTIFS; to average, use AVERAGEIFS.

Syntax

English Excel=SUMIFS(sum_range, criteria_range1, criteria1, ...)
Turkish Excel=ÇOKETOPLA(toplam_aralığı; ölçüt_aralığı1; ölçüt1; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
sum_rangeRequiredThe cells to add, e.g. the amount column D2:D50.
criteria_range1RequiredThe range tested by the first condition. It must have the same number of rows and columns as sum_range.
criteria1RequiredThe first condition: "West", ">=10000", "KBL*", a cell (G2) or a joined expression such as ">="&DATE(2025,10,1). Further conditions are added the same way, as range and criteria pairs.

Examples

1Sum by region and product

The conditions are in F2 and G2. The formula in H2 adds only the rows whose region is West and whose product is Cable.

H2fx=SUMIFS(D2:D8,B2:B8,F2,C2:C8,G2)
Sum by region and product: example table
ABCDEFGH
1DateRegionProductAmountRegionProductTotal
228-Sep-2025WestCable4,200.00WestCable19,800.00
32-Oct-2025WestCable12,500.00
46-Oct-2025NorthSocket8,300.00
514-Oct-2025WestSocket4,750.50
621-Oct-2025SouthCable9,900.00
730-Oct-2025WestCable3,100.00
83-Nov-2025NorthCable15,200.00

Three rows meet both conditions, and their total is 19,800.00. The socket sale in the West and the cable sale in the South are left out.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Sum one month's sales with a date range

F2 gives the date column twice: on or after 1 October and before 1 November. G2 adds the region as a third condition on top of the same range.

F2fx=SUMIFS(D2:D8,A2:A8,">="&DATE(2025,10,1),A2:A8,"<"&DATE(2025,11,1))
Sum one month's sales with a date range: example table
ABCDEFG
1DateRegionProductAmountOctober TotalOctober, West
228-Sep-2025WestCable4,200.0038,550.5020,350.50
32-Oct-2025WestCable12,500.00
46-Oct-2025NorthSocket8,300.00
514-Oct-2025WestSocket4,750.50
621-Oct-2025SouthCable9,900.00
730-Oct-2025WestCable3,100.00
83-Nov-2025NorthCable15,200.00
  • F2=SUMIFS(D2:D8,A2:A8,">="&DATE(2025,10,1),A2:A8,"<"&DATE(2025,11,1))
  • G2=SUMIFS(D2:D8,A2:A8,">="&DATE(2025,10,1),A2:A8,"<"&DATE(2025,11,1),B2:B8,"West")

October's five rows add up to 38,550.50, and the West's share of them is 20,350.50. The rows dated 28 September and 3 November fall outside the range.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3One rep's invoices above a set amount

The range you add can also be a criteria range. F2 adds Emma's invoices of 10,000 or more.

F2fx=SUMIFS(C2:C7,B2:B7,E2,C2:C7,">=10000")
One rep's invoices above a set amount: example table
ABCDEF
1InvoiceRepAmountRep10,000 or more
2F-1001Emma12,500.00Emma22,500.00
3F-1002Liam18,750.00
4F-1003Emma9,999.90
5F-1004Emma10,000.00
6F-1005Olivia26,400.00
7F-1006Emma4,200.00

Two of Emma's invoices qualify; F-1004, exactly 10,000, is included thanks to ">=": 22,500.00.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#VALUE!

Cause: One of the criteria ranges is not the same size as the sum range (D2:D50 against B2:B49).

Fix: Start and end every range on the same rows.

Result is 0

Cause: No row meets all the conditions together. Often a criterion is spelled differently from the list, or the dates are stored as text.

Fix: Copy the criteria from the list or put them in cells; check that the date column really holds dates.

Wrong column added

Cause: Out of SUMIF habit, the sum range was put last; Excel adds the first range and tests the conditions against the wrong columns.

Fix: In SUMIFS the sum range is always the first argument.

Good to know

  • Put the conditions in cells (G2, H2) instead of inside the formula; then you can change the summary without touching the formula.
  • You can use SUMIFS for a single condition too; the argument order stays the same and adding a condition later is easy.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Give me a summary table of sales totals by region and product group

Convert to table

Bring your sales list into the tool and type this. Nexell builds each cell of the summary with conditional sum formulas and shows it in the preview first; in the downloaded file, the summary changes when you change an amount.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I sum values between two dates with SUMIFS?

Give the date column twice: =SUMIFS(D2:D50,A2:A50,">="&DATE(2025,10,1),A2:A50,"<="&DATE(2025,10,31)). If the dates are in cells, use ">="&G1 and "<="&H1.

How do I use OR logic with SUMIFS?

SUMIFS always joins its conditions with AND. To add either of two regions, add two formulas together: =SUMIFS(D2:D50,B2:B50,"West")+SUMIFS(D2:D50,B2:B50,"South").

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and the sum range comes last. SUMIFS takes several conditions and the sum range comes first.

Related functions

All math functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free