ExcelFlux

SUMIF function

SUMIF adds the values from rows that meet one condition.

Math and trigonometry

Where can you use it?

Reporting, manufacturing and engineering teams. When a total, rounding step or numerical transformation is needed, use the example to see which operation fits your inputs.

Scenario in the workbook

Total sales by region. Start the criteria and sum ranges on the same row.

A worked example from the workbook

RowABC
6RegionStatusAmount
7NorthPaid120
8SouthPaid75
9NorthOpen40
10NorthPaid80
11SouthOpen0
North
=SUMIF(A7:A11,"North",C7:C11)

Expected result240

See the inputs and instructions for all 3 examples

Syntax

SUMIF(range, criteria, [sum_range])

Copyable formulas

English Excel · en-US
=SUMIF(A2:A5,D2,B2:B5)
Turkish Excel · tr-TR
=ETOPLA(A2:A5;D2;B2:B5)

Sample data

Enter the headers and sample records in A1:B5.

Location Hours
L01 8
L02 6
L01 7.5
L03 5
  • D2 = L01

Enter the formula in E2.

To try the Turkish formula, use the sample data on the Turkish page.

Expected result

15.5

How it works

Column A is checked against the criterion and column B supplies the values to add. Set D2 to L99 for a result of 0. Use SUMIFS when a second criterion such as month is needed.

Syntax

SUMIF(range, criteria, [sum_range])

Compatibility

Excel for Microsoft 365 and Excel 2016, 2019, 2021, and 2024.

Worked examples

Total sales by region. Start the criteria and sum ranges on the same row.

Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.

Row A B C
6 Region Status Amount
7 North Paid 120
8 South Paid 75
9 North Open 40
10 North Paid 80
11 South Open 0
Case Formula Expected result
North =SUMIF(A7:A11,"North",C7:C11) 240
South =SUMIF(A7:A11,"South",C7:C11) 75
West =SUMIF(A7:A11,"West",C7:C11) 0
Microsoft Excel reference