{"id":3630,"date":"2021-04-09T15:25:56","date_gmt":"2021-04-09T07:25:56","guid":{"rendered":"https:\/\/owa.sodexo-cn.com\/blog\/?p=3630"},"modified":"2021-04-09T15:25:56","modified_gmt":"2021-04-09T07:25:56","slug":"excel-condition-judgment-is-simple-ii","status":"publish","type":"post","link":"https:\/\/owa.sodexo-cn.com\/blog\/?p=3630","title":{"rendered":"Excel condition judgment is simple II"},"content":{"rendered":"\n<p>This week we continue to share a set of Excel conditional judgment related formulas. I hope it will be helpful to your work.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">COUNTIF<\/h4>\n\n\n\n<p>The syntax is as below:<br>=COUNTIF (condition region, specify condition)<br>As shown in the figure below, to count all transactions in No.1 sales department, the formula in F2 is: =COUNTIF(B:B,E2)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"553\" height=\"309\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/178.jpg\" alt=\"\" class=\"wp-image-3623\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/178.jpg 553w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/178-300x168.jpg 300w\" sizes=\"auto, (max-width: 553px) 100vw, 553px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">COUNTIFS<\/h4>\n\n\n\n<p>The syntax is as below:<br>=COUNTIFS (condition region 1, condition region 1, condition region 2, condition region 2\u2026)<br>As shown in the figure below, It need to count the total transactions in No.1 sales department with a single amount more than 500 (ten thousand Yuan).<br>The formula in F2 cell is: =COUNTIFS(B:B,E2,C:C,&#8221;&gt;500&#8243;)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"554\" height=\"207\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/179.jpg\" alt=\"\" class=\"wp-image-3624\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/179.jpg 554w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/179-300x112.jpg 300w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">AVERAGEIF and AVERAGEIFS<\/h4>\n\n\n\n<p>The function of AVERAGEIF is to calculate the average according to the specified conditions.<br>The function of AVERAGEIFS is to calculate the average based on multiple conditions.<br>It\u2019s syntax is like function SUMIF and SUMIFS.<br>As shown in the figure below, the average sales in No.1 sales department should be calculated.<br>Formula is: =AVERAGEIF(B:B,E2,C:C)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"553\" height=\"291\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/180.jpg\" alt=\"\" class=\"wp-image-3625\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/180.jpg 553w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/180-300x158.jpg 300w\" sizes=\"auto, (max-width: 553px) 100vw, 553px\" \/><\/figure>\n\n\n\n<p>To calculate the average value in No.1 sales department with a single amount more than 500 (ten thousand yuan).<br>Formula is: =AVERAGEIFS(C:C,B:B,E2,C:C,\u201d&gt;500\u201d)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"554\" height=\"205\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/181.jpg\" alt=\"\" class=\"wp-image-3626\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/181.jpg 554w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/181-300x111.jpg 300w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">MINIFS and MAXIFS<\/h4>\n\n\n\n<p>MINIFS and MAXIFS are used to count the minimum and maximum values that meet multiple conditions respectively.<br>Their syntax is like function SUMIFS.<br>As shown in the figure below, calculate the maximum sales for the department in column E.<br>The formula in E2 cell is: =MAXIFS(C:C,A:A,E3)<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"554\" height=\"332\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/182.jpg\" alt=\"\" class=\"wp-image-3627\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/182.jpg 554w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2021\/04\/182-300x180.jpg 300w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/figure>\n\n\n\n<p>Well, that&#8217;s all we have to share today. Wish you have a good day!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This week we continue to share a set of Excel condition &#8230; <a title=\"Excel condition judgment is simple II\" class=\"read-more\" href=\"https:\/\/owa.sodexo-cn.com\/blog\/?p=3630\" aria-label=\"\u9605\u8bfb Excel condition judgment is simple II\">\u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3630","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"views":1745,"_links":{"self":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3630"}],"version-history":[{"count":2,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3630\/revisions"}],"predecessor-version":[{"id":3633,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3630\/revisions\/3633"}],"wp:attachment":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}