How to use SUMIF and SUMIFS functions in Excel

How to use SUMIF and SUMIFS functions in Excel

Excel is a powerful tool for performing calculations with data. One of the most useful features of Excel is the ability to sum values based on certain conditions. This can help you analyze your data and find insights that might otherwise be hidden.

In this article, we will explain how to use the SUMIF and SUMIFS functions in Excel, which are designed for conditional summing. We will also show you some examples of how to apply these functions to different scenarios.

What is the SUMIF function?

The SUMIF function is used to sum the values in a range that meet a single condition. For example, you can use the SUMIF function to sum the sales of a specific product, or the expenses of a specific category.

The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

The arguments are:

• range: The range of cells that you want to evaluate by the criteria. This argument is required.
• criteria: The condition that defines which cells to sum. This argument is required. You can enter the criteria as a number, text, logical expression, cell reference, or another Excel function.
• sum_range: The range of cells that you want to sum. This argument is optional. If you omit it, Excel will use the same range as the first argument.

Here are some examples of how to use the SUMIF function:

• To sum the sales of apples in column B, based on the product names in column A, you can use:

=SUMIF(A2:A9, “Apples”, B2:B9)

• To sum the expenses that are greater than $500 in column C, based on the amounts in column B, you can use:

=SUMIF(B2:B9, “>500”, C2:C9)

• To sum the commissions earned by Tom in column D, based on the salesperson names in column C, you can use:

=SUMIF(C2:C9, “Tom”, D2:D9)

What is the SUMIFS function?

The SUMIFS function is used to sum the values in a range that meet multiple conditions. For example, you can use the SUMIFS function to sum the sales of a specific product in a specific region, or the expenses of a specific category in a specific month.

The syntax of the SUMIFS function is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The arguments are:

• sum_range: The range of cells that you want to sum. This argument is required.
• criteria_range1: The first range of cells that you want to evaluate by the first criteria. This argument is required.
• criteria1: The first condition that defines which cells to sum. This argument is required.
• criteria_range2, criteria2, …: Additional ranges and their associated conditions. These arguments are optional. You can enter up to 127 pairs of criteria ranges and criteria.

Here are some examples of how to use the SUMIFS function:

• To sum the sales of apples in column B, based on the product names in column A and the regions in column C, you can use:

=SUMIFS(B2:B9, A2:A9, “Apples”, C2:C9, “East”)

• To sum the expenses that are greater than $500 and less than $1000 in column C, based on the amounts in column B and the categories in column A, you can use:

=SUMIFS(C2:C9, B2:B9, “>500”, B2:B9, “<1000”, A2:A9, “Travel”)

• To sum the commissions earned by Tom or Sarah in column D, based on the salesperson names in column C and the dates in column B, you can use:

=SUMIFS(D2:D9, C2:C9,{“Tom”,“Sarah”}, B2:B9,“<4/1/2023”)

Difference between SUMIF and SUMIFS

The main difference between SUMIF and SUMIFS is that SUMIF can only handle one condition, while SUMIFS can handle multiple conditions. Therefore, SUMIFS is more versatile and flexible than SUMIF.

Another difference is that SUMIF has a different order of arguments than SUMIFS. In SUMIF, the range argument comes first, followed by the criteria and then the sum_range. In SUMIFS, the sum_range argument comes first, followed by pairs of criteria ranges and criteria.

A third difference is that SUMIF can handle array criteria (such as {“Tom”,“Sarah”}), while SUMIFS cannot. However, you can use multiple criteria ranges and criteria with logical operators (such as OR or AND) to achieve similar results with SUMIFS.

How to use wildcard characters with SUMIF and SUMIFS

Wildcard characters are special symbols that can match any character or sequence of characters in a text value. You can use wildcard characters with SUMIF and SUMIFS to make your criteria more flexible and dynamic.

The two most common wildcard characters are:

• The asterisk () matches any sequence of characters. For example, "A" matches any text value that begins with A, such as Apples, Artichokes, or Avocados.
• The question mark (?) matches any single character. For example, “A?” matches any two-character text value that begins with A, such as AB, AC, or AD.

You can also use the tilde (~) to escape a wildcard character if you want to match it literally. For example, “~?” matches the question mark character itself, not any single character.

Here are some examples of how to use wildcard characters with SUMIF and SUMIFS:

• To sum the sales of products that begin with A and end with S in column B, based on the product names in column A, you can use:

=SUMIF(A2:A9, “A*S”, B2:B9)

• To sum the expenses of categories that have four letters and start with T in column C, based on the category names in column A, you can use:

=SUMIF(A2:A9, “T???”, C2:C9)

• To sum the commissions earned by salespersons whose names contain an O in column D, based on the salesperson names in column C, you can use:

=SUMIF(C2:C9, “O”, D2:D9)

• To sum the sales of products that have a question mark in their names in column B, based on the product names in column A, you can use:

=SUMIF(A2:A9, “~?”, B2:B9)

How to use SUMIF and SUMIFS functions in Excel with example table:

Excel is a powerful tool for performing calculations with data. One of the most useful features of Excel is the ability to sum values based on certain conditions. This can help you analyze your data and find insights that might otherwise be hidden.

In this article, we will explain how to use the SUMIF and SUMIFS functions in Excel with an example table, which are designed for conditional summing. We will also show you some examples of how to apply these functions to different scenarios.

What is the SUMIF function?

The SUMIF function is used to sum the values in a range that meet a single condition. For example, you can use the SUMIF function to sum the sales of a specific product, or the expenses of a specific category.

The syntax of the SUMIF function is:

=SUMIF(range, criteria, [sum_range])

The arguments are:

• range: The range of cells that you want to evaluate by the criteria. This argument is required.
• criteria: The condition that defines which cells to sum. This argument is required. You can enter the criteria as a number, text, logical expression, cell reference, or another Excel function.
• sum_range: The range of cells that you want to sum. This argument is optional. If you omit it, Excel will use the same range as the first argument.

What is the SUMIFS function?

The SUMIFS function is used to sum the values in a range that meet multiple conditions. For example, you can use the SUMIFS function to sum the sales of a specific product in a specific region, or the expenses of a specific category in a specific month.

The syntax of the SUMIFS function is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The arguments are:

• sum_range: The range of cells that you want to sum. This argument is required.
• criteria_range1: The first range of cells that you want to evaluate by the first criteria. This argument is required.
• criteria1: The first condition that defines which cells to sum. This argument is required.
• criteria_range2, criteria2, …: Additional ranges and their associated conditions. These arguments are optional. You can enter up to 127 pairs of criteria ranges and criteria.

Example table

To illustrate how to use the SUMIF and SUMIFS functions in Excel, we will use the following example table:

Product	Region	Sales	Cost	Profit
Apples	North	100	50	50
Apples	South	150	75	75
Bananas	North	200	100	100
Bananas	South	250	125	125
Oranges	North	300	150	150
Oranges	South	350	175	175

The table has five columns: Product, Region, Sales, Cost, and Profit. The data range is A2:E7.

Examples of using SUMIF and SUMIFS

Here are some examples of how to use the SUMIF and SUMIFS functions with the example table:

• To sum the sales of apples in column C, based on the product names in column A, you can use:

=SUMIF(A2:A7, “Apples”, C2:C7)

This formula returns 250, which is the sum of sales where the product name is “Apples”.

• To sum the profit of products that are sold in the south region in column E, based on the region names in column B, you can use:

=SUMIF(B2:B7, “South”, E2:E7)

This formula returns 375, which is the sum of profit where the region name is “South”.

• To sum the cost of products that are not oranges in column D, based on the product names in column A, you can use:

=SUMIF(A2:A7,“<>Oranges”,D2:D7)

This formula returns 350, which is the sum of cost where the product name is not “Oranges”. The <> operator means “not equal to”.

• To sum the sales of products that are sold in both north and south regions in column C, based on the region names in column B, you can use:

=SUMIFS(C2:C7,B2:B7,“North”,B2:B7,“South”)

This formula returns 0, which is the sum of sales where both conditions are met. Since there are no products that are sold in both regions, the result is zero.

• To sum the profit of products that are either apples or bananas in column E, based on the product names in column A, you can use:

=SUMIFS(E2:E7,A2:A7,{“Apples”,“Bananas”})

This formula returns 350, which is the sum of profit where either condition is met. The array constant {“Apples”,“Bananas”} means that the product name can be “Apples” or “Bananas”.

Conclusion

In this article, we have explained how to use the SUMIF and SUMIFS functions in Excel with an example table, which are designed for conditional summing. We have also shown you some examples of how to apply these functions to different scenarios.

We hope you have found this article helpful and informative. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading!

Tweet