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?”