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:

SUMIF Syntax

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:

CriteriaCorrect criteria definition
Less than 0“<0”
Greater than 0“>0”
Equals to 0“=0”
Less or equals to 0“<=0”
Greater or equals to 0“>=0”
Different than 0“<>0”
Blank cells“”
Not blank cells“<>”
Text in cell begins with abc“abc*”
Text in cell ends with abc“*abc”

Examples

Example 1 – basic usage of SUMIF

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

SUMIF Example

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.

SUMIF Example

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.

SUMIF Example

Example 2 – criteria defined in a specific cell

The table below presents sales revenue divided by product category:

SUMIF Example

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

SUMIF Example

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;

SUMIF Example

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

SUMIF Example

Example 3 – criteria defined as text

The table below contains sales revenue divided by city:

SUMIF Example

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.

SUMIF Example

Received result:

SUMIF Example

File with examples – download

One thought on “How to use SUMIF function?

Leave a Reply

Your email address will not be published.