{"id":416971,"date":"2022-03-16T18:00:19","date_gmt":"2022-03-16T15:00:19","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-sumif-in-microsoft-excel\/"},"modified":"2022-03-16T18:00:19","modified_gmt":"2022-03-16T15:00:19","slug":"how-to-use-sumif-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-sumif-in-microsoft-excel\/","title":{"rendered":"#How to Use SUMIF in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2ea2347a3a0\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2ea2347a3a0\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-sumif-in-microsoft-excel\/#%E2%80%9CHow_to_Use_SUMIF_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Use SUMIF in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-sumif-in-microsoft-excel\/#Use_SUMIF_For_a_Single_Cell_Range\" >Use SUMIF For a Single Cell Range<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-sumif-in-microsoft-excel\/#Use_SUMIF_With_Number_Criteria_for_Multiple_Ranges\" >Use SUMIF With Number Criteria for Multiple Ranges<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-sumif-in-microsoft-excel\/#Use_SUMIF_With_Text_Criteria_for_Multiple_Ranges\" >Use SUMIF With Text Criteria for Multiple Ranges<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_SUMIF_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Use SUMIF in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Adding numbers together in Microsoft Excel is a basic calculation that can use the SUM function. What if you want to add those values but only if they meet certain conditions? This is when the SUMIF function comes in.<\/p>\n<p>With SUMIF, you can add the values in the cells you specify as long as they meet specific criteria. Maybe you want to find the total sales but only for certain products or the total revenue but only for particular locations.<\/p>\n<p>If your Excel sheet is set up in a way that your calculation isn\u2019t easily determined, the SUMIF function and its formula can help.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_SUMIF_For_a_Single_Cell_Range\"><\/span>Use SUMIF For a Single Cell Range<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for the function is <code>SUMIF(cell_range, criteria, sum_range)<\/code> where the first two arguments are required. Because <code>sum_range<\/code> is optional, you can add numbers in one range that correlate to criteria in another.<\/p>\n<p>To get the basic feel of the function and its arguments, let\u2019s start by using a single range of cells without the optional argument.<\/p>\n<p>You could\u00a0<a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/696837\/how-to-calculate-the-sum-of-cells-in-excel\/\">add values in a cell range only if they are greater than a certain amount. Enter the following formula, replacing the cell references and criteria with your own.<\/p>\n<pre>=SUMIF(C2:C7,\"&gt;25000\")<\/pre>\n<p>This formula adds the numbers in the cell range C2 through C7 only if they are greater than 25,000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789381\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SingleGreaterThan-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using greater than for a single cell range\" width=\"530\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>On the flip side, you can add numbers that are less than a certain amount using this formula:<\/p>\n<pre>=SUMIF(B2:B7,\"&lt;10000\")<\/pre>\n<p>This adds the numbers in cells B2 through B7 only if they are less than 10,000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789380\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SingleLessThan-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using less than for a single cell range\" width=\"530\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For one more example, you can add numbers that are the same amount with this formula:<\/p>\n<pre>=SUMIF(A2:A7,\"5000\")<\/pre>\n<p>This adds the numbers in cells A2 through A7 only if they are exactly 5,000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789379\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SingleEquals-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using equals for a single cell range\" width=\"520\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_SUMIF_With_Number_Criteria_for_Multiple_Ranges\"><\/span>Use SUMIF With Number Criteria for Multiple Ranges<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now let\u2019s put that conditional argument to work, <code>sum_range<\/code>. Here we\u2019re <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/462166\/how-to-create-expense-and-income-spreadsheets\/\">calculating expenses and revenue. With SUMIF, we can calculate the revenue for locations whose expenses meet our criteria and vice versa.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Create Expense and Income Spreadsheets in Microsoft Excel<\/em><\/strong><\/p>\n<p>With this formula you can add the revenue in cells C2 through C7 only if the expenses in cells B2 through B7 are less than 10,000.<\/p>\n<pre>=SUMIF(B2:B7,\"&lt;10000\",C2:C7)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789382\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleLessThan-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using less than for multiple cells\" width=\"580\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Using the following formula, you can add the expenses in cells B2 through B7 only if the revenue in cells C2 through C7 is greater than 25,000.<\/p>\n<pre>=SUMIF(C2:C7,\"&gt;25000\",B2:B7)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789383\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleGreaterThan-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using greater than for multiple cells\" width=\"585\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You can also replace the actual value in the formula with one contained in a cell. For instance, this formula adds the numbers in B2 through B7 if the value in C2 through C7 is greater than the value in cell D2.<\/p>\n<pre>=SUMIF(C2:C7,\"&gt;\"&amp;D2,B2:B7)<\/pre>\n<p>This formula uses the greater than symbol (\u201c&gt;\u201d) and cell D2 (&amp;D2).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789384\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleGreaterThanCell-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using greater than a cell\" width=\"565\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Use_SUMIF_With_Text_Criteria_for_Multiple_Ranges\"><\/span>Use SUMIF With Text Criteria for Multiple Ranges<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Maybe the values you want to add correlate to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/769974\/how-to-count-cells-with-text-in-microsoft-excel\/\">text rather than numbers. Here we have types, products, and sales. Using SUMIF, you can add values in the Sales column for products that meet certain conditions in the other columns.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Cells With Text in Microsoft Excel<\/em><\/strong><\/p>\n<p>In this example, you can add the sales in cells C2 through C7 only if the text in cells A2 through B7 equals the word <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">App<\/a>arel.<\/p>\n<pre>=SUMIF(A2:B7,\"Apparel\",C2:C7)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789385\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleTextWord-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF equals a word for multiple cells\" width=\"575\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>For another example, you can add the sales in cells C2 through C7 for products in cells B2 through B7 that end in \u201cts.\u201d<\/p>\n<pre>=SUMIF(B2:B7,\"*ts\",C2:C7)<\/pre>\n<p>In this formula, the asterisk (*) is a wildcard representing any letters before \u201cts\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789387\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleTextWildcard-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF ends with letters in multiple cells\" width=\"545\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>One more example uses our Shoes product whose Type is blank.<\/p>\n<pre>=SUMIF(A2:B7,\"\",C2:C7)<\/pre>\n<p>In this formula the quotation marks are side-by-side with no space between them. This gives us the sales for Shoes as seen below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-789386\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/MultipleTextBlank-ExcelSUMIF.jpg?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUMIF using a blank for multiple cells\" width=\"520\" height=\"290\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The SUMIF function in Excel allows you to take a basic equation and spice it up to fit your needs. It\u2019s super handy when adding numbers isn\u2019t as simple as two plus two.<\/p>\n<p>For additional help, take a look at how to find the function you need in Excel.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/789373\/how-to-use-sumif-in-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use SUMIF in Microsoft Excel&#8221; Adding numbers together in Microsoft Excel is a basic calculation that can use the SUM function. What if you want to add those values but only if they meet certain conditions? This is when the SUMIF function comes in. With SUMIF, you can add the values in the&#8230;<\/p>\n","protected":false},"author":1,"featured_media":416972,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-416971","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/416971","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=416971"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/416971\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/416972"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=416971"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=416971"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=416971"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}