{"id":28976,"date":"2023-05-10T23:34:20","date_gmt":"2023-05-10T21:34:20","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=28976"},"modified":"2023-11-29T19:57:12","modified_gmt":"2023-11-29T18:57:12","slug":"how-to-use-sumifs-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/how-to-use-sumifs-function-in-excel\/","title":{"rendered":"How to Use SUMIFS Function in Excel"},"content":{"rendered":"<p>To fully utilize Excel&#8217;s capabilities as a tool for managing data, it&#8217;s crucial to have a solid grasp of all of its features. The SUMIFS function, which <strong>enables you to sum values in a range that satisfies multiple conditions<\/strong>, is one such function.<\/p>\n<p>When you need to <strong>analyze data depending on many criteria<\/strong>, the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/math\/sumif\/\" target=\"_blank\" rel=\"noopener\">SUMIFS function<\/a> comes in handy. It is a <strong>flexible function<\/strong> that may be applied in a wide range of situations, from determining sales numbers for particular goods or geographical areas to examining client information based on demographics or purchase patterns.<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">SYNTAX<\/span><\/strong><\/p>\n<p>=SUMIFS(<span style=\"color: #0000ff;\">Sum_Range<\/span>,<span style=\"color: #ff6600;\">Criteria_Range1<\/span>,<span style=\"color: #008000;\">Criteria1<\/span>,<span style=\"color: #ff6600;\">Criteria_Range2<\/span>,<span style=\"color: #008000;\">Criteria2<\/span>\u2026)<\/p>\n<ul>\n<li><span style=\"color: #0000ff;\">Sum_Range<\/span> (required) &#8211; The range of cells to sum.<\/li>\n<li><span style=\"color: #ff6600;\">Criteria_Range1<\/span> (required) &#8211; The range that is tested using Criteria1. Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.<\/li>\n<li><span style=\"color: #008000;\">Criteria1<\/span> (required) &#8211; The criteria that define which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, &#8220;&gt;32&#8221;, B4, &#8220;apples&#8221;, or &#8220;32&#8221;.<\/li>\n<li><em><span style=\"color: #ff6600;\">Criteria_Range2<\/span>, <span style=\"color: #008000;\">Criteria2<\/span>, \u2026 (optional) &#8211; Additional ranges and their associated criteria.<\/em><\/li>\n<\/ul>\n<p>You can enter up to 127 range\/criteria pairs.<\/p>\n<p>Let us look at a few examples to help us understand this function better.<\/p>\n<h4>Download the Excel Workbook below to follow along and understand how to highlight duplicates in Excel &#8211;<\/h4>\n<h4><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/SUMIFS-in-Excel.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">SUMIFS-in-Excel.xlsx<\/span><\/a><\/h4>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">Example 1<\/span><\/strong><\/p>\n<p>In this example, we are <strong>looking up the total sales amount for product 1001 in the East region<\/strong>. Let us understand it with the help of a step-by-step tutorial.<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 1: <\/span><\/strong>Enter the <strong>SUMIFS<\/strong> function in cell F3.<\/p>\n<p><strong>=SUMIFS(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28982\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1594\" height=\"1108\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM.png 1594w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM-300x209.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM-1024x712.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM-768x534.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.07-AM-1536x1068.png 1536w\" sizes=\"(max-width: 1594px) 100vw, 1594px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 2: <\/span><\/strong> Enter the first argument &#8211; <strong>Sum_range<\/strong>. Here we have selected the range C2:C89 as it contains all the sales figures.<\/p>\n<p><strong>=SUMIFS(C2:C89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28983\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1586\" height=\"1142\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM.png 1586w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM-300x216.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM-1024x737.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM-768x553.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.22-AM-1536x1106.png 1536w\" sizes=\"(max-width: 1586px) 100vw, 1586px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 3: <\/span><\/strong> Enter the second argument &#8211;<strong> Criteria_range1<\/strong>. Here we have selected B2:B89 range as it contains the region of sale details. We will apply our East region criteria in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,B2:B89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28984\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1590\" height=\"1180\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM.png 1590w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM-300x223.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM-1024x760.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM-768x570.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.33-AM-1536x1140.png 1536w\" sizes=\"(max-width: 1590px) 100vw, 1590px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 4: <\/span><\/strong> Enter the third argument &#8211; <strong>Criteria1. <\/strong>Here we have entered &#8220;East&#8221; as we want the sum of sales in the East region.<\/p>\n<p><strong>=SUMIFS(C2:C89,B2:B89,&#8221;East&#8221;,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28985\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1584\" height=\"1112\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM.png 1584w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM-300x211.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM-1024x719.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM-768x539.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.45-AM-1536x1078.png 1536w\" sizes=\"(max-width: 1584px) 100vw, 1584px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 5: <\/span><\/strong>Enter the fourth argument &#8211; <strong>Criteria_range2. <\/strong>Here we have selected A2:A89 range as it contains the product ID details. We will apply our product 1001 criteria in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,B2:B89,&#8221;East&#8221;,A2:A89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28986\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1584\" height=\"1188\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM.png 1584w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM-300x225.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM-1024x768.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM-768x576.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.26.56-AM-1536x1152.png 1536w\" sizes=\"(max-width: 1584px) 100vw, 1584px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 6: <\/span><\/strong>Enter the fifth argument &#8211; <strong>Criteria2. <\/strong>Here we have entered &#8220;1001&#8221; as we want the sum of sales of product 1001.<\/p>\n<p><strong>=SUMIFS(C2:C89,B2:B89,&#8221;East&#8221;,A2:A89,&#8221;1001&#8243;)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone wp-image-28987 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1578\" height=\"1224\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM.png 1578w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM-300x233.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM-1024x794.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM-768x596.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.05-AM-1536x1191.png 1536w\" sizes=\"(max-width: 1578px) 100vw, 1578px\" \/><\/a><\/p>\n<p>As we can see, the SUMIFS function returns the sum of sales of product 1001 in the East region, applying multiple criteria at once.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone wp-image-28988\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"753\" height=\"657\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM.png 1392w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM-300x262.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM-1024x893.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.14-AM-768x670.png 768w\" sizes=\"(max-width: 753px) 100vw, 753px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">Example 2<\/span><\/strong><\/p>\n<p>Here we will find the<strong> sales for product 1002 in the north and south region<\/strong> in cells F3 and G3. Let&#8217;s understand this step by step<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 1: <\/span><\/strong>Enter the <strong>SUMIFS<\/strong> function in cell F3.<\/p>\n<p><strong>=SUMIFS(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28989\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1612\" height=\"1144\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM.png 1612w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM-300x213.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM-1024x727.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM-768x545.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.29-AM-1536x1090.png 1536w\" sizes=\"(max-width: 1612px) 100vw, 1612px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 2: <\/span><\/strong> Enter the first argument &#8211; <strong>Sum_range<\/strong>. Here we have selected C2:C89 range as it contains all the sales figures of which we want the sum.<\/p>\n<p><strong>=SUMIFS(C2:C89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28990\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1582\" height=\"1144\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM.png 1582w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM-300x217.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM-1024x740.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM-768x555.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.41-AM-1536x1111.png 1536w\" sizes=\"(max-width: 1582px) 100vw, 1582px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 3: <\/span><\/strong>Enter the second argument &#8211;<strong> Criteria_range1<\/strong>. Here we have selected A2:A89 range as it contains the product ID details. We will apply our product 1002 criteria in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28991\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1614\" height=\"1148\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM.png 1614w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM-300x213.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM-1024x728.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM-768x546.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.27.51-AM-1536x1093.png 1536w\" sizes=\"(max-width: 1614px) 100vw, 1614px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 4: <\/span><\/strong> Enter the third argument &#8211; <strong>Criteria1. <\/strong>Here we have entered &#8220;1002&#8221; as we want the sum of sales of product 1002.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1002&#8243;,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28992\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1686\" height=\"1148\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM.png 1686w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM-300x204.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM-1024x697.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM-768x523.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.02-AM-1536x1046.png 1536w\" sizes=\"(max-width: 1686px) 100vw, 1686px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 5: <\/span><\/strong> Enter the fourth argument &#8211; <strong>Criteria_range2. <\/strong>Here we have selected B2:B89 range as it contains the region of sale details. We will apply our North and South criteria in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1002&#8243;,B2:B89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28993\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1798\" height=\"1144\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM.png 1798w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM-300x191.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM-1024x652.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM-768x489.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.11-AM-1536x977.png 1536w\" sizes=\"(max-width: 1798px) 100vw, 1798px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 6: <\/span><\/strong> Enter the fifth argument &#8211; <strong>Criteria2. <\/strong>Here we have entered {&#8220;North&#8221;, &#8220;South&#8221;} as we want the sum of sales in the regions North and South separately in cells F3 and G3.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1002&#8243;,B2:B89,{&#8220;North&#8221;,&#8221;South&#8221;})<\/strong><\/p>\n<p><em>Make sure to keep your criteria in curly brackets.<\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28994\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1778\" height=\"1144\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM.png 1778w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM-300x193.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM-1024x659.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM-768x494.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.32-AM-1536x988.png 1536w\" sizes=\"(max-width: 1778px) 100vw, 1778px\" \/><\/a><\/p>\n<p>As we can see, the SUMIFS function returns the sum of sales of product 1002 in North and South regions, applying multiple criteria at once.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28995\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1774\" height=\"1146\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM.png 1774w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM-300x194.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM-1024x662.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM-768x496.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.28.41-AM-1536x992.png 1536w\" sizes=\"(max-width: 1774px) 100vw, 1774px\" \/><\/a><\/p>\n<p>This can also be achieved if we used 2 SUMIFS functions separately as we did in our first example.<\/p>\n<p>&nbsp;<\/p>\n<p><strong><span style=\"color: #ff0000!important;\">Example 3<\/span><\/strong><\/p>\n<p>In this example, we are <strong>looking up the total sales amount for product 1001 but only when the sales amount is greater than $20,000.<\/strong><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 1: <\/span><\/strong>Enter the <strong>SUMIFS<\/strong> function in cell F3.<\/p>\n<p><strong>=SUMIFS(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28998\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1470\" height=\"1070\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM.png 1470w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM-300x218.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM-1024x745.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.28-AM-768x559.png 768w\" sizes=\"(max-width: 1470px) 100vw, 1470px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 2: <\/span><\/strong> Enter the first argument &#8211; <strong>Sum_range<\/strong>. Here we have selected C2:C89 range as it contains all the sales figures of which we want the sum.<\/p>\n<p><strong>=SUMIFS(C2:C89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-28999\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1584\" height=\"1064\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM.png 1584w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM-300x202.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM-1024x688.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM-768x516.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.36-AM-1536x1032.png 1536w\" sizes=\"(max-width: 1584px) 100vw, 1584px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 3: <\/span><\/strong>Enter the second argument &#8211;<strong> Criteria_range1<\/strong>. Here we have selected A2:A89 range as it contains the product ID details. We will apply our product 1002 criteria in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-29000\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1512\" height=\"1064\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM.png 1512w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM-300x211.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM-1024x721.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.45-AM-768x540.png 768w\" sizes=\"(max-width: 1512px) 100vw, 1512px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 4: <\/span><\/strong> Enter the third argument &#8211; <strong>Criteria1. <\/strong>Here we have entered &#8220;1001&#8221; as we want the sum of sales of product 1001.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1001&#8243;,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-29001\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1492\" height=\"1062\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM.png 1492w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM-300x214.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM-1024x729.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.48.56-AM-768x547.png 768w\" sizes=\"(max-width: 1492px) 100vw, 1492px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 5: <\/span><\/strong> Enter the fourth argument &#8211; <strong>Criteria_range2. <\/strong>Here we have selected range C2:C89 as it contains the sales amount. We will apply the rule &#8211; greater than $20,000 in this range.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1002&#8243;,C2:C89,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-29002\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1654\" height=\"1066\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM.png 1654w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM-300x193.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM-1024x660.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM-768x495.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.06-AM-1536x990.png 1536w\" sizes=\"(max-width: 1654px) 100vw, 1654px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000!important;\">STEP 6: <\/span><\/strong> Enter the fifth argument &#8211; <strong>Criteria2. <\/strong>Here we have entered &gt;20000 as we want the sum of sales for transactions where the sales amount is greater than $20,000.<\/p>\n<p><strong>=SUMIFS(C2:C89,A2:A89,&#8221;1002&#8243;,C2:C89,&#8221;&gt;&#8221;&amp;20000)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"How to Use SUMIFS Function in Excel\"  class=\"alignnone size-full wp-image-29003\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM.png\" alt=\"How to Use SUMIFS Function in Excel\" width=\"1660\" height=\"1068\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM.png 1660w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM-300x193.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM-1024x659.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM-768x494.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/04\/Screen-Shot-2023-05-01-at-2.49.24-AM-1536x988.png 1536w\" sizes=\"(max-width: 1660px) 100vw, 1660px\" \/><\/a><\/p>\n<p>As you can see, Excel has provided us with the total sales amount for transactions where the product ID is 1001 and the transaction amount is greater than $20,000.<\/p>\n<p>Even though SUMIFS function is an extremely useful function, it has <strong>some restrictions<\/strong> that you should be aware of:<\/p>\n<ul>\n<li><strong>Range size:<\/strong> The SUMIFS function may not be appropriate for some complicated data analysis scenarios since it can become slow or cumbersome when employed with very big data sets.<\/li>\n<li>Multiple criteria are available with the SUMIFS function, but they <strong>might not be able to capture all the subtleties of complex data sets<\/strong>, such as interactions between various variables.<\/li>\n<li><strong>Syntax difficulty<\/strong>: Using the SUMIFS function might be challenging, especially if you&#8217;re juggling several criteria or intricate data sets. To utilize it properly, one must have a thorough understanding of Excel&#8217;s features and syntax.<\/li>\n<li>The <strong>size of each range must be uniform<\/strong>. A #VALUE error will be returned if the supplied ranges don&#8217;t match.<\/li>\n<li>All range arguments must be actual ranges; <strong>an array cannot be used<\/strong> with the SUMIFS function.<\/li>\n<li>SUMIFS is <strong>not case-sensitive<\/strong>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>To fully utilize Excel&#8217;s capabilities as a tool for managing data, it&#8217;s crucial to have a solid grasp of all of its features. The SUMIFS function, which enables you to sum values in a range that satisfies multiple conditions, is one such function. When you need to analyze data depending on many criteria, the SUMIFS [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":29200,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"How to Use SUMIFS Function in Excel","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[15,292],"tags":[58,1222,1221],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28976"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=28976"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/28976\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/29200"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=28976"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=28976"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=28976"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}