# How to use SUMIF function?

Quite often we need to calculate sum of values which meet specific conditions, for example are less or greater than 0. For such cases, Excel has special function – SUMIF, which is used to sum values from a given range, which meet specified criteria.

## Syntax of SUMIF

=SUMIF(range, criteria, [sum_range])

Range – required; defines range of cells which will be evaluated by criteria;
Criteria – required; criteria which specify cells to be summed up. Examples: greater than 0, less than 0, greater or equal than 0 etc.;
Sum_range– optional; this is actual cell range which will be added, if these cells are other than those specified in range argument.

To use SUMIF function, start typing “= SUMIF(” in the formula bar, and then use the fx icon to open the window with arguments specification:

## How to define SUMIF criteria?

The criteria for SUMIF function must be defined in a specific way. In table below, you can find the most common examples for SUMIF criteria:

## Examples

### Example 1 – basic usage of SUMIF

Table below contains a list of unpaid invoices with their value, date of issue and days overdue:

In this example, I would like to calculate the total value of invoices, which amount is at least \$1,000, and the value of invoices which are already overdue.

Total values for invoices above \$1,000:

=SUMIF(D2:D16;”>=1000″)

Range – D2:D16, which is range where we can find amounts of each invoice;
Criteria – we need to find amounts greater or equal to 1000, so required entry is “>=1000”,
Sum_range – in this example this argument is not necessary and will be left empty.

Total value for overdue invoices:

=SUMIF(C2:C16;”>0″;D2:D16)

Range – C2:C16 – cell range including overdue days information;
Criteria – we are looking for invoices overdue more than 0 days, so required entry is “>0”,
Sum_range – D2:D16 – we want to sum up invoice values entered to column D.

### Example 2 – criteria defined in a specific cell

The table below presents sales revenue divided by product category:

Cells A20, A21 and A22 contains names of 3 categories for which I would like to calculate total revenues:

To calculate total revenue for fruit, syntax of the function should be written as following:

=SUMIF(A2:A17;A20;B2:B17)

Range – cells which will be evaluated by criteria, so it should be A2:A17, as they contain category names;
Criteria – it should be “A20”, because required criteria (in this case it’s. category name) is written down in cell A20;
Sum_range – cell range containing data to be added – in this example this is B2:B17, where we can find revenues;

The formula entered in this way can be dragged down, but remember to use correct cell references with \$ sign:

### Example 3 – criteria defined as text

The table below contains sales revenue divided by city:

In this example, I would like to calculate the revenues only for Cracow. To do this, the syntax of SUMIF function should be defined as below:

=SUMIF(A2:A17;”=Cracow”;B2:B17)

Range – A2:A17, containing city names;
Kryteria – “=Cracow” – as we are looking only Cracow within A2:A17 cell range;
Sum_range – B2:B17, this is cell range to be summed up.