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
- How to define SUMIF criteria?
- Example 1 – basic usage of SUMIF
- Example 2 – criteria defined in a specific cell
- Example 3 – criteria defined as text
- File with examples – download
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:
| Criteria | Correct 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:
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.
Received result:
One thought on “How to use SUMIF function?”