{"id":522,"date":"2019-04-16T17:45:47","date_gmt":"2019-04-16T14:45:47","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=522"},"modified":"2019-05-06T12:45:30","modified_gmt":"2019-05-06T09:45:30","slug":"learn-how-to-use-the-countifs-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/learn-how-to-use-the-countifs-function\/","title":{"rendered":"Learn how to use the COUNTIFS function"},"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-countifs-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"COUNTIFS  (opens in a new tab)\">COUNTIFS <\/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\/El3yecNe9OU?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\/countifs.xlsx\">countifs<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/countifs.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, 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 COUNTIFS, the count of the gross total of the retail sales to the eastern district in cell J6.<\/p>\n\n\n\n<p>Without COUNTIFS, 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 count of the gross total of the bulk sales to the western district, in cell K6, using COUNTIFS.<\/p>\n\n\n\n<p>We select the cell and type the = character, followed by the\nname of the COUNTIFS function and a left parenthesis.<\/p>\n\n\n\n<p>We define the cell range of our first criterion, in 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. The\ncriterion is that the type of sale is bulk, so we type, inside double quotes, =Bulk.\nWe 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, from C2 to C43 and then the criterion which is the Western district. Take note that we chose not to use the = 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 6.<\/p>\n\n\n\n<p>Following the same syntax, we can use up to 127 criteria\nranges and criteria in a COUNTIFS function.<\/p>\n\n\n\n<p>Let\u2019s see one more example in cell L6, where we have to calculate the number of small sales, under 100 units, of black products.<\/p>\n\n\n\n<p>We type our function, our first 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 the\nless than sign of course since this is only valid for the = 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 count of all the\nproducts with color starting with the letter b.<\/p>\n\n\n\n<p>We use b followed by an asterisk sign, thus including color blue in our calculation. But this is not what we need to do in our current example. 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. COUNTIFS is one of them.<\/p>\n","protected":false},"author":2,"featured_media":524,"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":[35,11,75,14],"class_list":["post-522","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-countifs","tag-data","tag-logical","tag-statistical"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/countifs.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-8q","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/522","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=522"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/522\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/524"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=522"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=522"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=522"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}