{"id":527,"date":"2019-04-16T17:53:48","date_gmt":"2019-04-16T14:53:48","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=527"},"modified":"2019-05-06T12:46:23","modified_gmt":"2019-05-06T09:46:23","slug":"learn-how-to-get-the-best-out-of-the-sumifs-functions","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/learn-how-to-get-the-best-out-of-the-sumifs-functions\/","title":{"rendered":"Learn how to get the best out of the SUMIFS functions"},"content":{"rendered":"\n<p>Excel has provided us with several functions to help us make sense of huge pools of data. <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-sumifs-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"SUMIFS  (opens in a new tab)\">SUMIFS <\/a><\/strong>is one of them.<\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/xkqqyHnIpGQ?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/SUMIFS.xlsx\">SUMIFS<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/SUMIFS.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In the current worksheet, we can see a small example of the data\nwe need to handle.<\/p>\n\n\n\n<p>This is the sales of a company for the year 2015.<\/p>\n\n\n\n<p>We have the date of the Sale, the salesman, the district at\nwhich the sale took place, the kind of sale (bulk or retail), the type of the\nproduct (in this example we only differentiate the products by color), the\nunits sold, the price per unit, and the gross total of the sale.<\/p>\n\n\n\n<p>Of course, at a normal working environment, these data would\nonly be a tiny fragment of the actual data we would have to analyze.<\/p>\n\n\n\n<p>We have already calculated, using SUMIFS, the sum of the gross total of the retail sales to the eastern district, in cell J6.<\/p>\n\n\n\n<p>Without SUMIFS, such an automatic calculation, would be\nimpossible and the manual one would require several workhours.<\/p>\n\n\n\n<p>Let\u2019s suppose now, that we need to calculate the sum of the gross total of the bulk sales to the western district, in cell K6, using SUMIFS.<\/p>\n\n\n\n<p>We select the cell and type the \u201c=\u201d character, followed by\nthe name of the SUMIFS function and a left parenthesis.<\/p>\n\n\n\n<p>Then we enter the cell area we need to sum, in our case the\narea from H2 to H43.<\/p>\n\n\n\n<p>After that, we define the cell range of our first criterion,\nin our example this is the column of the kind of sale, from D2 to D43.<\/p>\n\n\n\n<p>Following, is the criterion for this cell range. In our case,\nthe criterion is, that type of sale is bulk, so we type, inside double quotes, \u201c=\nBulk\u201d. We should note here, that we can omit the equals sign if we want.<\/p>\n\n\n\n<p>Then we proceed to our next criterion.<\/p>\n\n\n\n<p>First, we pick the cell range, in our case from C2 to C43\nand then the criterion which is the \u201cWestern\u201d district. Take note that we chose\nnot to use the \u201c=\u201d sign now.<\/p>\n\n\n\n<p>Our function is ready and if we close the parenthesis and\npress enter, we will see the result which is $7000.<\/p>\n\n\n\n<p>Following the same syntax, we can use up to 127 criteria\nranges and criteria in a SUMIFS function.<\/p>\n\n\n\n<p>Let\u2019s see one more example in cell L6, where we have to\ncalculate the sales of under 100 units of black products.<\/p>\n\n\n\n<p>We type our function, the sum range same as before, and our\nfirst criterion range, in this example the cell area from F2 to F43.<\/p>\n\n\n\n<p>Then we type the criterion, less than 100. We cannot omit\nthe less than sign, since this is only valid for the equals sign.<\/p>\n\n\n\n<p>And then, as before, we proceed to our second criterion.<\/p>\n\n\n\n<p>Let\u2019 s note here, that we can use wildcards to our criteria.\nFor example, we could, instead of black color, need the sum of all the products\nwith color starting with the letter b.<\/p>\n\n\n\n<p>We use b followed by an asterisk sign, thus including color\nblue in our calculation. But, this is not what we need to do in our current\nexample.<\/p>\n\n\n\n<p>So, we type black and press enter, which shows our desired result.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Excel has provided us with several functions to help us make sense of huge pools of data. SUMIFS is one of them.<\/p>\n","protected":false},"author":2,"featured_media":529,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[11,75,15,36],"class_list":["post-527","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-data","tag-logical","tag-math","tag-sumifs"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/sumifs.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8v","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/527","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=527"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/527\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/529"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=527"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}