{"id":29428,"date":"2023-06-09T00:52:39","date_gmt":"2023-06-08T22:52:39","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=29428"},"modified":"2024-03-22T18:31:36","modified_gmt":"2024-03-22T17:31:36","slug":"sumproduct-with-if","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/","title":{"rendered":"How to Use SUMPRODUCT with IF in Excel \u2013 3 Perfect Examples"},"content":{"rendered":"<p><strong><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29472\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64-1024x576.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1024\" height=\"576\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64-1024x576.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64-300x169.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64-768x432.png 768w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/4D2AA4CE-1934-45FE-98A7-E60E0AC2CF64.png 1280w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/a><\/strong>Microsoft Excel is a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/free-microsoft-excel-online-course\/\" target=\"_blank\" rel=\"noopener\">popular spreadsheet software<\/a> that offers a vast array of functions and formulas that can assist in complex data analysis.<\/p>\n<p>Among these, the SUMPRODUCT function stands out as a versatile tool, allowing users to perform calculations on multiple ranges of data simultaneously.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">SUMPRODUCT<\/a> with <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\">IF<\/a> takes data analysis to a whole new level by incorporating boolean logic and conditional calculations.<\/p>\n<p>In this article, we will explore the ins and outs of using SUMPRODUCT with the IF function, providing comprehensive examples and explanations to help you harness its full potential.<\/p>\n<p>Let us look at each of these points one by one!<\/p>\n<h4>Make sure to download this Excel Workbook and follow along to understand how to use SUMPRODUCT with IF in Excel:<\/h4>\n<p><a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/SUMPRODUCT-IF.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\"><strong>download excel workbook<\/strong><\/span><span class=\"postTitle px-3\">SUMPRODUCT-IF.xlsx<\/span><\/a><\/p>\n<h4><em><strong>Want to How to Use SUMPRODUCT in Excel?<\/strong><\/em><\/h4>\n<p>*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***<\/p>\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/cKN4l65cLn4?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\" data-mce-fragment=\"1\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">?<\/span><\/iframe><\/p>\n<div>\n<div style=\"float: left;\"><span style=\"text-decoration: underline;\"><strong><a href=\"https:\/\/youtu.be\/cKN4l65cLn4\" target=\"_blank\" rel=\"noopener noreferrer nofollow\">Watch on YouTube<\/a> <\/strong><\/span>and give it a thumbs up &#x1f44d;<\/div>\n<div>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p>&nbsp;<\/p>\n<\/div>\n<\/div>\n<p><strong><span id=\"1\" style=\"color: #ff0000;\">Syntax<\/span><\/strong><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#SUMPRODUCT\" title=\"SUMPRODUCT\">SUMPRODUCT<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#IF\" title=\"IF\">IF<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_1\" title=\"Examples of SUMPRODUCT with IF &#8211; Example 1\">Examples of SUMPRODUCT with IF &#8211; Example 1<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_2\" title=\"Examples of SUMPRODUCT with IF &#8211; Example 2\">Examples of SUMPRODUCT with IF &#8211; Example 2<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_3\" title=\"Examples of SUMPRODUCT with IF &#8211; Example 3\">Examples of SUMPRODUCT with IF &#8211; Example 3<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/sumproduct-with-if\/#Additional_Uses_for_Sumproduct_with_IF\" title=\"Additional Uses for Sumproduct with IF\">Additional Uses for Sumproduct with IF<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"SUMPRODUCT\"><\/span><strong><span style=\"color: #ff0000;\">SUMPRODUCT<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>=SUMPRODUCT(<span style=\"color: #3366ff;\">array1,<\/span> <span style=\"color: #339966;\">[array2], [array3], &#8230;<\/span>)<\/strong><\/p>\n<p>The SUMPRODUCT function syntax has the following arguments:<\/p>\n<ul>\n<li><strong><span style=\"color: #3366ff;\">array1<\/span> <\/strong>&#8211; The first array argument whose components you want to multiply and then add. (Required)<\/li>\n<li><strong><span style=\"color: #339966;\">[array2], [array3], &#8230; <\/span><\/strong>&#8211; Array arguments 2 to 255 whose components you want to multiply and then add. (Optional)<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"IF\"><\/span><strong><span style=\"color: #ff0000;\">IF<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>=IF(<strong><span style=\"color: #3366ff;\">logical_test,<\/span> <span style=\"color: #339966;\">value_if_true,<\/span> <span style=\"color: #ff9900;\">[value_if_false]<\/span><\/strong>)<\/p>\n<p>The IF function syntax has the following arguments:<\/p>\n<ul>\n<li><strong><span style=\"color: #3366ff;\">logical_test<\/span> <\/strong>&#8211; The condition you want to test. (Required)<\/li>\n<li><strong><span style=\"color: #339966;\">value_if_true<\/span> <\/strong>&#8211; The value that you want to be returned if the result of logical_test is TRUE. (Required)<\/li>\n<li><strong><span style=\"color: #ff9900;\">value_if_false<\/span><\/strong> &#8211; The value that you want to be returned if the result of logical_test is FALSE. (Optional)<\/li>\n<\/ul>\n<p>Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it&#8217;s false.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_1\"><\/span><strong><span id=\"2\" style=\"color: #ff0000;\">Examples of SUMPRODUCT with IF &#8211; <span style=\"color: #ff0000;\">Example 1<\/span><\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To better grasp the concept of Sumproduct, let&#8217;s begin with a simple example. Consider a <strong>dataset containing sales quantities and prices<\/strong> for different products:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.53.23-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29432\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.53.23-PM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"361\" height=\"131\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.53.23-PM.png 540w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.53.23-PM-300x109.png 300w\" sizes=\"(max-width: 361px) 100vw, 361px\" \/><\/a><\/p>\n<p>If we want to<strong> calculate the total sales value<\/strong>, we can use Sumproduct as follows:<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the formula.<\/p>\n<p><strong>=SUMPRODUCT(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29436\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"691\" height=\"212\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM.png 1128w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM-300x92.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM-1024x314.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.40-PM-768x236.png 768w\" sizes=\"(max-width: 691px) 100vw, 691px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2:<\/span><\/strong> Enter the first argument, which is the first array that we want to sum and multiply.<\/p>\n<p><strong>=SUMPRODUCT(B2:B5,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29435\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"697\" height=\"199\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM.png 1260w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM-300x86.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM-1024x293.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.54.53-PM-768x219.png 768w\" sizes=\"(max-width: 697px) 100vw, 697px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3:<\/span><\/strong> Enter the second argument, which is the second array that we want to sum and multiply.<\/p>\n<p><strong>=SUMPRODUCT(B2:B5,C2:C5)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29434\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"716\" height=\"195\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM.png 1262w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM-300x82.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM-1024x279.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.03-PM-768x209.png 768w\" sizes=\"(max-width: 716px) 100vw, 716px\" \/><\/a><\/p>\n<p>Let&#8217;s see what the end result looks like.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29433\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"722\" height=\"209\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM.png 1128w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM-300x87.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM-1024x296.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-04-at-11.55.09-PM-768x222.png 768w\" sizes=\"(max-width: 722px) 100vw, 722px\" \/><\/a><\/p>\n<p>As you can see, the <strong>formula multiplies the corresponding values in each array we selected and then adds up all the values obtained<\/strong>. This gives us the final sales value of our example.<\/p>\n<p>Before we proceed with our next example, let&#8217;s understand a new concept &#8211; <strong>Boolean Logic<\/strong>!<\/p>\n<p>In Excel, Boolean logic is the<strong> use of logical operators to assess and manipulate data based on binary conditions such as TRUE or FALSE<\/strong>. Excel includes a number of<strong> logical operators, such as AND, OR, and NOT<\/strong>, that allow users to construct sophisticated logical statements. To execute conditional computations and logical tests, Boolean logic is frequently<strong> used in conjunction with other Excel functions<\/strong>, such as the IF function.<\/p>\n<p>Users can make decisions, filter data, perform sophisticated computations, and generate dynamic reports based on specific situations by inserting boolean logic into formulas.<\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_2\"><\/span><strong><span id=\"2\" style=\"color: #ff0000;\">Examples of SUMPRODUCT with IF &#8211; <span style=\"color: #ff0000;\">Example 2<\/span><\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Let us proceed with our previous example for better clarity. Here we want to <strong>find the total sales amount of all products where the units sold is at least 10 units<\/strong>.<\/p>\n<p>In this scenario, we can <strong>use Sumproduct with the IF function to apply the condition<\/strong>. Let&#8217;s break it down.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1:<\/span><\/strong> Enter the SUMPRODUCT formula.<\/p>\n<p><strong>=SUMPRODUCT(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29438\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1494\" height=\"360\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png 1494w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-300x72.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-1024x247.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-768x185.png 768w\" sizes=\"(max-width: 1494px) 100vw, 1494px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Enter the IF function.<\/p>\n<p><strong>=SUMPRODUCT(IF(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29439\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1506\" height=\"358\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM.png 1506w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM-300x71.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM-1024x243.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.53-AM-768x183.png 768w\" sizes=\"(max-width: 1506px) 100vw, 1506px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Enter the criteria for the IF function. We want to check only the values that are above or equal to 10 units in the array B2:B5.<\/p>\n<p><strong>=SUMPRODUCT(IF(B2:B5&gt;=10,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29440\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1496\" height=\"360\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM.png 1496w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM-300x72.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM-1024x246.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.09-AM-768x185.png 768w\" sizes=\"(max-width: 1496px) 100vw, 1496px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4: <\/span><\/strong>Now we will <strong>enter the value we want if our criteria is met<\/strong>. As we want the values of units sold only, we will enter the<strong> array containing the units i.e. B2:B5.<\/strong><\/p>\n<p><strong>=SUMPRODUCT(IF(B2:B5&gt;=10,B2:B5,<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29441\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1504\" height=\"368\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM.png 1504w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM-300x73.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM-1024x251.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.30-AM-768x188.png 768w\" sizes=\"(max-width: 1504px) 100vw, 1504px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5: <\/span><\/strong>Now we will <strong>enter the value we want if our criteria is not met<\/strong>. Here, it is 0.<\/p>\n<p><strong>=SUMPRODUCT(IF(B2:B5&gt;=10,B2:B5,0),<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29442\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1502\" height=\"364\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM.png 1502w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM-300x73.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM-1024x248.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.42-AM-768x186.png 768w\" sizes=\"(max-width: 1502px) 100vw, 1502px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 6: <\/span><\/strong>We now need to select our second array for the sumproduct function which is C2:C5.<\/p>\n<p><strong>=SUMPRODUCT(IF(B2:B5&gt;=10,B2:B5,0),C2:C5)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29443\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1490\" height=\"370\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM.png 1490w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM-300x74.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM-1024x254.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.04.59-AM-768x191.png 768w\" sizes=\"(max-width: 1490px) 100vw, 1490px\" \/><\/a><\/p>\n<p>Let&#8217;s understand the IF part of our formula a bit more.<\/p>\n<ul>\n<li>The IF function checks if the quantity sold in each row (B2:B5) is greater than or equal to 10.<\/li>\n<li>If the condition is met, it returns the quantity sold; otherwise, it returns 0.<\/li>\n<li>Sumproduct then multiplies the resulting array with the price array (C2:C5) and sums the products.<\/li>\n<\/ul>\n<p>As a result, we get the total sales amount of all products where the units sold are at least 10 units.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29444 \" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"947\" height=\"276\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM.png 1210w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM-300x87.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM-1024x298.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.13.28-AM-768x223.png 768w\" sizes=\"(max-width: 947px) 100vw, 947px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Examples_of_SUMPRODUCT_with_IF_%E2%80%93_Example_3\"><\/span><strong><span id=\"2\" style=\"color: #ff0000;\">Examples of SUMPRODUCT with IF &#8211; <span style=\"color: #ff0000;\">Example 3<\/span><\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Now let us try a more complex example with multiple criteria. Here, we will be using the boolena logi instead of the IF function.<\/p>\n<p>Suppose we want to <strong>calculate the total sales value for products sold in the North region during Quarter 1<\/strong>. We can utilize Sumproduct with the IF function to apply multiple conditions simultaneously.<\/p>\n<p>The formula would be:<br \/>\n<strong>=SUMPRODUCT((Region=&#8221;North&#8221;)*(Quarter=&#8221;Q1&#8243;)*(Quantity Sold), Price)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29438\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1494\" height=\"360\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM.png 1494w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-300x72.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-1024x247.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.03.44-AM-768x185.png 768w\" sizes=\"(max-width: 1494px) 100vw, 1494px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 1: <\/span><\/strong>Enter the sumproduct formula.<\/p>\n<p><strong>=SUMPRODUCT(<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29455\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1266\" height=\"418\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1.png 1266w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1-300x99.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1-1024x338.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.11-AM-1-768x254.png 768w\" sizes=\"(max-width: 1266px) 100vw, 1266px\" \/><\/a><\/p>\n<p>Now we are going to simplify our formula by creating a single array. We are going to simultaneously check our criteria.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 2: <\/span><\/strong>Let&#8217;s start with our first criteria, which is<strong> checking for sales in the North region within our array of B2:B6.<\/strong><\/p>\n<p><strong>=SUMPRODUCT((B2:B6=&#8221;North&#8221;)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29456\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1166\" height=\"408\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1.png 1166w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1-300x105.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1-1024x358.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.26-AM-1-768x269.png 768w\" sizes=\"(max-width: 1166px) 100vw, 1166px\" \/><\/a><\/p>\n<p>Now we know that the boolean logic changes values to TRUE and FALSE, or it can be seen as 1 and 0 respectively. Hence all values in our array that meet the criteria will be turned to 1, and those that do not meet the criteria will be turned to 0.<\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 3: <\/span><\/strong>Now we are going to multiply this criteria array with our second criteria array, so we are entering the * operator.<\/p>\n<p><strong>=SUMPRODUCT((B2:B6=&#8221;North&#8221;)*<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29461\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1156\" height=\"414\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM.png 1156w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM-300x107.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM-1024x367.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.34.38-AM-768x275.png 768w\" sizes=\"(max-width: 1156px) 100vw, 1156px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 4:<\/span><\/strong>Similarly, we will now <strong>enter our second criteria, which is searching for Q1 in the array C2:C6<\/strong>.<\/p>\n<p><strong>=SUMPRODUCT((B2:B6=&#8221;North&#8221;)*(C2:C6=&#8221;Q1&#8243;)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29457\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1156\" height=\"408\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1.png 1156w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1-300x106.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1-1024x361.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.20.43-AM-1-768x271.png 768w\" sizes=\"(max-width: 1156px) 100vw, 1156px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">STEP 5: <\/span><\/strong>This entire argument that we have entered will count as a single argument for the sumproduct function. We will have an array of 5 rows and 1 column consisting of 1s and 0s.<\/p>\n<p>1 where all of our multiple criteria meet, and 0 where at least one criteria do not meet.<\/p>\n<p>Now we need to enter our second argument, which is the first array that we want to sum and multiply, which is the array containing the quantity sold.<\/p>\n<p><strong>=SUMPRODUCT((B2:B6=&#8221;North&#8221;)*(C2:C6=&#8221;Q1&#8243;),D2:D6,<\/strong><\/p>\n<p><strong><span style=\"color: #ff0000;\"><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29458\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1266\" height=\"414\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1.png 1266w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1-300x98.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1-1024x335.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.02-AM-1-768x251.png 768w\" sizes=\"(max-width: 1266px) 100vw, 1266px\" \/><\/a><\/span><\/strong><\/p>\n<p>Now we need to enter our second argument, which is the second array that we want to sum and multiply, which is the array containing the price of the products.<\/p>\n<p><strong>=SUMPRODUCT((B2:B6=&#8221;North&#8221;)*(C2:C6=&#8221;Q1&#8243;),D2:D6,E2:E6)<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-29459\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"1368\" height=\"422\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM.png 1368w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM-300x93.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM-1024x316.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-05-at-12.21.09-AM-768x237.png 768w\" sizes=\"(max-width: 1368px) 100vw, 1368px\" \/><\/a><\/p>\n<p>As we can see, the result of our formula gives us the total sales value for products sold in the North region during Quarter 1.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29475\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"696\" height=\"313\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM.png 1370w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM-300x135.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM-1024x460.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-12.53.28-AM-768x345.png 768w\" sizes=\"(max-width: 696px) 100vw, 696px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Additional_Uses_for_Sumproduct_with_IF\"><\/span><strong><span id=\"3\" style=\"color: #ff0000;\">Additional Uses for Sumproduct with IF<\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The combination of Sumproduct with the IF function opens up a world of <strong>possibilities for advanced data analysis<\/strong> in Excel. Here are a few more applications:<\/p>\n<p><strong>1. Weighted Sum:<\/strong><\/p>\n<p>Sumproduct with IF can be used to <strong>assign varying weights to each element<\/strong> in a range before summing them up. For example, if you have <strong>stock data with weights and returns<\/strong>, you can calculate the weighted return by multiplying each item by its weight and then summing the products.<\/p>\n<p>In this example, we want to calculate the <strong>weighted return of the portfolio<\/strong> but we only <strong>want to include stocks with weights greater than 15%<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29480\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"618\" height=\"281\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM.png 1310w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM-300x136.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM-1024x466.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.27.19-AM-768x349.png 768w\" sizes=\"(max-width: 618px) 100vw, 618px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>2. Data Filtering:<\/strong><\/p>\n<p>Using Sumproduct with IF, you can<strong> filter and extract certain data from a dataset based on conditional criteria<\/strong>. You can <strong>retrieve precise subsets of data that fit your criteria by combining various conditions<\/strong>.<\/p>\n<p>In this example, we are using SUMPRODUCT with IF to get the<strong> sales amount<\/strong> when the three criteria are met &#8211; <strong>Region is North, Sales Rep is John and Quarter is 1<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29481\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"816\" height=\"584\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM.png 1446w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM-300x215.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM-1024x734.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-1.56.35-AM-768x550.png 768w\" sizes=\"(max-width: 816px) 100vw, 816px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><strong>3. Performance Evaluation:<\/strong><\/p>\n<p>Assume you have a <strong>dataset with student scores in various subjects<\/strong> and you want to calculate th<strong>e average grade for each student<\/strong> while only taking into account areas where they scored above a certain threshold. Sumproduct with IF allows you to include or omit subjects depending on defined criteria and calculate the average grade accordingly.<\/p>\n<p>In this example, we want to calculate the <strong>total score<\/strong> of the student for all subjects falling under <strong>Group A<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-29482\" title=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM.png\" alt=\"How to Use SUMPRODUCT with IF in Excel - 3 Perfect Examples\" width=\"700\" height=\"434\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM.png 1292w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM-300x186.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM-1024x636.png 1024w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2023\/06\/Screen-Shot-2023-06-07-at-2.34.45-AM-768x477.png 768w\" sizes=\"(max-width: 700px) 100vw, 700px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<h3><strong><span id=\"4\" style=\"color: #ff0000;\">Conclusion<\/span><\/strong><\/h3>\n<p>When paired with the IF function, Excel&#8217;s Sumproduct function enables users to do <strong>advanced data analysis and conditional computations<\/strong> effectively. You may <strong>unlock the full power of Sumproduct and derive important insights from your datasets by implementing boolean logic and applying numerous conditions<\/strong>.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/category\/formulas\/math\/sumproduct\/\" target=\"_blank\" rel=\"noopener\">Sumproduct with IF<\/a> provides a robust toolkit for<strong> solving difficult data analysis jobs<\/strong> in Excel, whether you need to <strong>do weighted calculations, filter data, or analyze performance<\/strong>. So go in, play with different scenarios, and unleash the entire power of Sumproduct with IF to take your data analysis talents to the next level.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft Excel is a popular spreadsheet software that offers a vast array of functions and formulas that can assist in complex data analysis. Among these, the SUMPRODUCT function stands out as a versatile tool, allowing users to perform calculations on multiple ranges of data simultaneously. SUMPRODUCT with IF takes data analysis to a whole new [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":29472,"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 SUMPRODUCT with IF in Excel \u2013 3 Perfect Examples","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[283,285],"tags":[1255,1256,1254],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29428"}],"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=29428"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/29428\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/29472"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=29428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=29428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=29428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}