Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria: 6 Methods

Method 1 – Multiple Sum Ranges & Criteria with Excel SUMIFS Function Using Comparison Operators

Steps:

  • Go to the cell where you want to insert the result (cell C13 in our case).
  • To sum up the Sales values, type the formula:
=SUMIFS(D5:D11,B5:B11,"USA",D5:D11,">=1500")+SUMIFS(H5:H11,F5:F11,"Canada",H5:H11,">=1300")
  • We can see the formula in the Formula Bar of the image below.

  • After inserting the formula, press the Enter key to get the result.
  • We can see the output in cell C13 of the screenshot below.

How Does the Formula Work?

  • D5:D11 & H5:H11 (first part): Indicate the sum_range.
  • B5:B11 & F5:F11: Denote the criteria_range1.
  • “USA” & “Canada”: Refer to the criteria1.
  • D5:D11 & H5:H11 (last part): Denote the criteria_range2.
  • “>=1500” & “>=1300”: Refer to the criteria2.
  • SUMIFS(D5:D11,B5:B11,”USA”,D5:D11,”>=1500″): Returns the summation of the Sales for USA with the Sales value greater than $1500.
  • SUMIFS(H5:H11,F5:F11,”Canada”,H5:H11,”>=1300″): Adds the Sales values for Canada that are greater than $1300.
  • SUMIFS(D5:D11,B5:B11,”USA”,D5:D11,”>=1500″)+SUMIFS(H5:H11,F5:F11,”Canada”,H5:H11,”>=1300″): Sums up the two values gained from the two SUMIFS formula.

Method 2 – Including Dates in the SUMIFS Function with Multiple Sum Ranges and Criteria

Steps:

  • Activate cell C13 to place the summation.
  • Insert the following formula in the cell (C13) for adding the Sales values:
=SUMIFS(D5:D11,B5:B11,"Apple",C5:C11,">="&TODAY()-10,C5:C11,"<="&TODAY())+SUMIFS(H5:H11,F5:F11,"Orange",G5:G11,">="&TODAY()-8,G5:G11,"<="&TODAY())
  • See the formula in the Formula Bar of the picture below.

  • Press the Enter key to find the result.
  • See the output in cell C13 of the screenshot below.

How Does the Formula Work?

  • &TODAY: Returns the current date.
  • SUMIFS(D5:D11,B5:B11,”Apple”,C5:C11,”>=”&TODAY()-10,C5:C11,”<=”&TODAY()): Adds the Sales for Apple with the Order Dates within 10 days before & including the current date.
  • SUMIFS(H5:H11,F5:F11,”Orange”,G5:G11,”>=”&TODAY()-8,G5:G11,”<=”&TODAY()): Sums up the Sales for Orange with the Order Dates within 8 days before & including the current date.
  • SUMIFS(D5:D11,B5:B11,”Apple”,C5:C11,”>=”&TODAY()-10,C5:C11,”<=”&TODAY())+SUMIFS(H5:H11,F5:F11,”Orange”,G5:G11,”>=”&TODAY()-8,G5:G11,”<=”&TODAY()): Returns the summation of the two values gained from the two SUMIFS formula.

Method 3 – Array Argument for Multiple Sum Ranges and Criteria with SUM & SUMIFS Functions

Steps:

  • Select cell C13.
  • To add the Sales values, insert the following formula in cell C13:
=SUM((SUMIFS(D5:D11,C5:C11,"Apple",B5:B11,{"USA","France"}))+(SUMIFS(H5:H11,G5:G11,"Orange",F5:F11,{"Canada","Denmark"})))
  • We can see the formula in the Formula Bar of the image below.
  • Press Ctrl + Shift + Enter (for array formula).

How Does the Formula Work?

  • SUMIFS(D5:D11,C5:C11,”Apple”,B5:B11,{“USA”,”France”}): Returns two summations of the Sales of Apple. The first one is for the orders by the USA and the second one is for France.
  • SUMIFS(H5:H11,G5:G11,”Orange”,F5:F11,{“Canada”,”Denmark”}): Returns two summations of the Sales of Orange. The first one is for the USA and the second one is for France.
  • SUM((SUMIFS(D5:D11,C5:C11,”Apple”,B5:B11,{“USA”,”France”}))+(SUMIFS(H5:H11,G5:G11,”Orange”,F5:F11,{“Canada”,”Denmark”}))): Returns the summation of the four values obtained from the two SUMIFS formula.
  • We can see the result in cell C13 of the screenshot below.


Method 4 – Using SUMIFS Function for Blank & Non-Blank Cells with Multiple Ranges & Criteria

Steps:

  • Select the cell (C13) where you want to insert the summation.
  • To get the summation of the Sales, type the following formula:
=SUMIFS(E5:E11,B5:B11,"USA",C5:C11,"<>",D5:D11,"=")+SUMIFS(J5:J11,G5:G11,"Canada",H5:H11,"<>",I5:I11,"=")

  • Get the result by pressing the Enter key (see screenshot).

How Does the Formula Work?

  • “<>”: Indicates the non-blank cells.
  • “=”: Denotes the blank cells.
  • SUMIFS(E5:E11,B5:B11,”USA”,C5:C11,”<>”,D5:D11,”=”): Adds the Sales for USA with the non-blank cells in the C5:C11 range and the blank cells in the D5:D11 range.
  • SUMIFS(J5:J11,G5:G11,”Canada”,H5:H11,”<>”,I5:I11,”=”): Sums up the Sales for Canada with the non-blank cells in the H5:H11 range and the blank cells in the I5:I11 range.
  • SUMIFS(E5:E11,B5:B11,”USA”,C5:C11,”<>”,D5:D11,”=”)+SUMIFS(J5:J11,G5:G11,”Canada”,H5:H11,”<>”,I5:I11,”=”): Returns the summation of the two values gained from the two SUMIFS formula.

Method 5 – Utilizing SUMIFS Function for Dynamic Criteria with Named Range

Steps:

  • Select the range C13:C14 to create the dynamic criteria.
  • Go to the Name Box (see screenshot).

  • Type any name (Countries) in the Name Box for the dynamic criteria.

  • Select cell C16 where you want to keep the output.
  • When you start inserting the formula and typing dynamic criteria (Countries). You will see it as a suggestion (see screenshot).
  • The formula is:
=SUMIFS(D5:D11,B5:B11,Countries)+SUMIFS(H5:H11,F5:F11,Countries)

  • Finish typing the formula to find the output.

  • After pressing Ctrl + Shift + Enter (for the older versions before Excel 2019), you will get the output in cells C16 and C17.
  • The value in cell C16 is the summation of Sales for Denmark, and the one in C17 is for France.

Use SUMIFS Function for Dynamic Criteria with Named Range

How Does the Formula Work?

  • SUMIFS(D5:D11,B5:B11,Countries): Adds the Sales for the range B5:B11 based on the dynamic criteria (Countries).
  • SUMIFS(H5:H11,F5:F11,Countries): Sums up the Sales for the range H5:H11 based on the dynamic criteria named Countries.
  • SUMIFS(D5:D11,B5:B11,Countries)+SUMIFS(H5:H11,F5:F11,Countries): Returns the summation of the two values gained from the two SUMIFS formula.

Method 6 – Applying Wildcard Character with SUMIFS Function for Multiple Ranges & Criteria

Steps:

  • Activate cell C13.
  • To find the summation, type the formula:
=SUMIFS(D5:D11,B5:B11,"USA",C5:C11,"*s*")+SUMIFS(H5:H11,F5:F11,"Canada",G5:G11,"*g*")

  • Press the Enter key to get the result (see the screenshot below).

How Does the Formula Work?

  • SUMIFS(D5:D11,B5:B11,”USA”,C5:C11,”*s*”): Adds the Sales for the USA with the Fruits containing ‘s’.
  • SUMIFS(H5:H11,F5:F11,”Canada”,G5:G11,”*g*”): Sums up the Sales.
  • Canada with the fruits containing ‘g’.
  • SUMIFS(D5:D11,B5:B11,”USA”,C5:C11,”*s*”)+SUMIFS(H5:H11,F5:F11,”Canada”,G5:G11,”*g*”): Returns the summation of the two values gained from the two SUMIFS formula.

Download Practice Workbook

Download the practice workbook from here.


Related Articles


<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo