HomeApplicationOffice informationHow to use SUMIF with 2 or more conditions in Excel

How to use SUMIF with 2 or more conditions in Excel

You already know the SUMIF function to calculate conditional sum in Excel, but you do not know how to use the SUMIF function with 2 or more conditions in Excel. So you let TipsMake.com find out in this article.

How to use SUMIF with 2 or more conditions in Excel Picture 1

Here TipsMake.com share to you how to use the SUMIFS function with 2 or more conditions in Excel, please follow along.

The SUMIF function is a function that sums 1 condition.If you want to sum with 2 or more conditions in Excel, you need to use the SUMIFS function.

Syntax of SUMIFS function:

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

Inside:

  1. Sum_range : a required argument, this is the range of cells to sum.
  2. Criteria_range1 : is a required argument, this is the range to be tested using Criteria1 .
  3. Criteria1 : required argument, this is the condition that identifies a cell within Criteria_range1 .

Criteria_range1 and Criteria1 are set as a search pair, whereby a range is searched according to specific criteria.

  1. Criteria_range2, Criteria2 . are optional arguments, these are additional ranges and conditions associated with the argument. The SUMIFS function supports up to 127 range of conditions / conditions.

For example , using the SUMIFS function with 2 or more conditions in Excel.

Suppose you have the data table as shown below:

How to use SUMIF with 2 or more conditions in Excel Picture 2

1. Total sale of iPhone 6s plus sold by Thu Ha staff

Because of the total sale proceeds, Sum_range is the total sum column D7: D15 .

According to this requirement, the SUMIFS function will have 2 pairs of range / condition:

  1. Scope 1 is A7: A15 with condition 1 is "iPhone 6s plus"
  2. Scope 2 is F7: F15, provided that condition 2 is "Thu Hà"

Thus, the SUMIFS function will look like this:

= SUMIFS (D7: D15, A7: A15, "iPhone 6s plus", F7: F15, "Thu Ha")

How to use SUMIF with 2 or more conditions in Excel Picture 3

2. Total amount of sales of BlueTuoch LP-V6 Speakers on June 10, 2019 sold by Thu Nguyet staff

Because of the total sale, Sum_range is D7: D15

This requirement has 3 conditions with the following range / condition pairs:

  1. Range 1 is A7: A15, provided that the "BlueTuoch LP-V6 Speaker"
  2. Range 2 is E7: E15 provided that "June 10, 2019"
  3. Range 3 is F7: F15 provided that "Thu Nguyet"

Thus the SUMIFS function syntax is as follows:

= SUMIFS (D7: D15, A7: A15, A10 , E7: E15, E10 , F7: F15, F11 )

How to use SUMIF with 2 or more conditions in Excel Picture 4

Above TipsMake.com showed you how to use the SUMIFS function with 2 or more conditions in Excel. Hopefully through this article, you will better understand the SUMIFS function in Excel to use summing when two or more conditions are required. Good luck!

5 ★ | 1 Vote

You should read it

May be interested

  • How to join PDF files with Foxit ReaderPhoto of How to join PDF files with Foxit Reader
    how to join pdf files with foxit reader. unlike the word document format that can be easily edited, copied, and pasted, the pdf is divided into pages and we can only stitch pages together. to join the pdf file, we will use foxit phantom software.
  • How to change the file extension XLSX to XLS to open in Excel 2003Photo of How to change the file extension XLSX to XLS to open in Excel 2003
    how to change the file extension xlsx to xls to open in excel 2003. you have a file in .xlsx format but the computer needs to open and install only excel 2003? don't worry, just follow the simple .xlsx to .xls extension instructions as below, you will get what you want.
  • What is a PivotTable? How to use PivotTable in ExcelPhoto of What is a PivotTable? How to use PivotTable in Excel
    excel's pivottable is a great tool that microsoft equips into excel so users can summarize, analyze, learn and present data more easily. what is a pivottable? how to use pivottable in excel
  • How to calculate time in Excel: Add and subtract timePhoto of How to calculate time in Excel: Add and subtract time
    excel can calculate the amount of time by adding / subtracting method to give you the result of the amount of time in minutes, hours, days, and months. the following software tips will guide you the most basic formulas. the problem is that you have time to complete two steps of work, and you want to know how many hours and minutes it will take to complete the job.
  • ROUNDUP function in Excel - Usage and examplesPhoto of ROUNDUP function in Excel - Usage and examples
    today, the software tips will guide you how to use the roundup function to round numbers (rounding up) in excel. roundup function structure function syntax: = roundup (number, num_digits). in which: + roundup: is the function name. + number: is the number to be rounded up. + num_digits: is the number of digits after the comma you want to round. for example:
  • Match function in Excel - Usage and illustrative examplesPhoto of Match function in Excel - Usage and illustrative examples
    the match function in excel finds a specified item in a specified range and, returns the relative position of that value in this range. function syntax and usage syntax: = match (lookup_value, lookup_array, [match_type]). in which: - match: is the function name. - lookup_value: is the value to search, be it text or numeric.

Office information