## How to Use the SUMIF Function and Formula in Excel

In this tutorial, you'll learn how to use the **SUMIF function** and formula in Excel. **SUMIF **function in Excel is a function to sums the values in a range of cells that meet single criteria.

### SUMIF Syntax

The syntax of the **SUMIF** function is as follows:

=

SUMIF(range,criteria, [sum_range])

### Arguments

: The range of cells that you want to be evaluated by the criteria you specify.*Range*: The criteria used to determine which cells to add.*Criteria*: [Optional]The actual cells in a range that you want to sum.*Sum_range*

### Remarks

- If
*sum_range***Excel SUMIF function**adds the cells specified in the*range* - Text
or*criteria*, which include mathematical or logical symbols, need to be enclosed in double quotation marks. If the specified criteria are numeric, double quotation marks are not required.*criteria* - You can use the
**wildcard characters***and**?**in criteria. **SUMIF**is not case-sensitive. So, for example,**JOHN**and**John**are treated the same.

### SUMIF Function Examples

The following table is a sample list of sales records we're going to use in our examples.

A | B | C | D | E | F | G | |

1 | Date | Code | Item Name | Quantity | Price ($) | Amount ($) | Employee |

2 | 10/20/2019 | Item1001 | Sony Desktop PC 1 | 2 | 1050 | 2100 | Roland |

3 | 10/20/2019 | Item1002 | Sony Desktop PC 2 | 2 | 950 | 1900 | James |

4 | 10/20/2019 | Item1001 | Sony Desktop PC 1 | 1 | 1250 | 1250 | James |

5 | 10/20/2019 | Item1003 | Sony Desktop PC 3 | 2 | 450 | 900 | Roland |

6 | 10/21/2019 | Item1002 | Sony Desktop PC 2 | 3 | 1000 | 3000 | Roland |

7 | 10/22/2019 | Item1001 | Sony Desktop PC 1 | 1 | 1190 | 1190 | Roland |

8 | 10/23/2019 | Item1003 | Sony Desktop PC 3 | 2 | 450 | 900 | James |

**SUMIF with DATE Function**

Let's say you want to sum all the values in the range **F2:F8** where the date is **10/20/2019**, then you can write the formula as follows:

=

SUMIF(A2:A8,DATE(2019,10,20),F2:F8)

Here, **A2:A8** is the criteria range, **DATE(2016,10,5)** is criteria, and **F2:F8** is the range of cells you want to sum.

**SUMIF** sums all the values in the range **F2**: **F8**, where the value of the cells in the **Date **column is equal to October 20th, 2019

The above formula returns 6150.

*Note that here we use the DATE function as criteria.*

If you use the text criteria, the SUMIF formula will look like this:

=

SUMIF(A2:A8,"10/20/2019",F2:F8)

This formula returns the same result (6150) as the previous formula.

**SUMIF with a Reference Criteria**

In this example, you'll learn how to use the reference criteria in the **SUMIF** function.

The formula using a reference in criteria would look like this:

=SUMIF(A2:A8,H2,F2:F8)

H2 is a cell reference to criteria.

**SUMIF with Logical Operators** **in Criteria**

In this example, I show you how to use logical operators, ">=" , "<=", "<" or ">" in a text criteria.

In this example, let's say you want to sum only the values of cells that are greater than 2000 in the range of cells.

You can write the **SUMIF** formula as follows:

=

SUMIF(F2:F8,">2000")

Here, as you can see, there are only two arguments and the third (**sum_range**) argument omitted. So, the range F2: F8 becomes both **range** and **sum_range**. Based on the specified condition above, **SUMIF** sums all the values that are greater than 2000 in the range **F2**: **F8**, and returns 5100.

Let's look at another example:

=SUMIF(A2:A8,">10/22/2019",F2:F8)

As you can see, the second argument is a text criterion that includes the logical operator (**>**). **SUMIF** sums all the values of cells that are greater than **10/22/2019** in column A and returns 900.

Next, let's look at an example that uses a logical operator (>=):

=SUMIF(A2:A8,">=10/22/2019",F2:F8)

**SUMIF** sums all the values of cells that are greater than or equal to **10/22/2019** in column A and returns 2090.

Similarly, to use a logical operator "**<=**" in criteria, you can write the **SUMIF** formula as follows:

=SUMIF(A2:A8,"<=10/20/2019",F2:F8)

**SUMIF** sums all the values of cells that are less than or equal to **10/202019** in column A and returns 6150.

You also can perform a partial matching in the **SUMIF** function. To perform a partial matching, you need to use the wildcard characters (*****) and (**?**) in text criteria.

**Partial Matching Using Asterisk (*)**

In this example, we use (*) in a text criterion.

Let's take a look at a formula below:

=SUMIF(C2:C8,"So*",F2:F8)

The criteria **So*** matches any text string that begins with **So**, and for the rest, everything would be fine. So, **SUMIF** sums all the values of cells that the item name starts with **So**.

The result of the above formula is 9990.

Note that, the wildcard characters (*) matches any sequence of characters.

**Partial Matching Using Question Mark (?)**

In this example, we use the wildcard (**?**) in a text criterion.

Let's take a look at the formula below.

=SUMIF(C2:C8,"???? Desktop PC",F2:F8)

Note that a wildcard (**?**) is different from (*****). It matches any single character.

The criteria **???? Desktop PC** matches any text string that begins with any four lengths text string and ends with **Desktop PC**.

**SUMIF** sums all the values of cells that the item name matches the specified condition above and returns 0.

**SUMIF is not Case-Sensitive**

SUMIF is not case-sensitive, so **James, james**, and **JAMES** text criteria are treated the same.

Let's take a look at the formula below.

=SUMIF(G2:G8,"James",F2:F8)

In the above formula, you can replace the criteria **James** with **JAMES**, **james** or **JaMeS**, because **SUMIF** treats the lowercase and uppercase characters the same.

If you want to sum all the values in a range of cells that meet more than one criteria, use **SUMIFS**.

In this tutorial, you've learned how to use the **Excel SUMIF function**. **SUMIF** is used to sum all the values in a range of cells that meet single criteria. If you want to specify multiple criteria, use the SUMIFS function.