{"id":4229,"date":"2022-04-08T14:10:32","date_gmt":"2022-04-08T06:10:32","guid":{"rendered":"https:\/\/owa.sodexo-cn.com\/blog\/?p=4229"},"modified":"2022-04-08T14:10:32","modified_gmt":"2022-04-08T06:10:32","slug":"simple-and-practical-use-of-several-function-formulas-ii","status":"publish","type":"post","link":"https:\/\/owa.sodexo-cn.com\/blog\/?p=4229","title":{"rendered":"Simple and practical use of several function formulas II"},"content":{"rendered":"\n<p>Hello everyone, today We continue to share some simple and practical uses of function formulas.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Sum in merged cells<\/h4>\n\n\n\n<p>As shown in the picture below, it needs to count the sales amount for each store.<br>At the same time, select the D2:D10 , enter the formula and press Ctrl+ Enter<br>=SUM(C2:C20)-SUM(D3:D20)<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"499\" height=\"403\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/272.gif\" alt=\"\" class=\"wp-image-4224\"\/><\/figure>\n\n\n\n<p>SUM(C2:C20) is the SUM of the current row and the following C column. SUM(D3:D20) is the SUM of all the categories other than this category.<br>Subtract these two totals and you get the sum of this category.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Limit input duplicate data<\/h4>\n\n\n\n<p>Select the A2:A10 cell area for which you want to enter data. Click \u201cData\u201d-&gt;\u201dData Validation\u201d<br>Select \u201cCustom\u201d in Allow, then input the formula:<br>=COUNTIF(A:A,A2)=1<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"522\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/273.gif\" alt=\"\" class=\"wp-image-4225\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Highlight duplicate data<\/h4>\n\n\n\n<p>Select the area of A2:A10 cells for which you have entered data and create a new format rule.<br>The formula is<br>=COUNTIF(A$2:A2,A2)&gt;1<br>Set the format of the highlight<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"522\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/274.gif\" alt=\"\" class=\"wp-image-4226\"\/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Use XLOOKUP to look up data<\/h4>\n\n\n\n<p>As shown in the picture below, query the department in column A based on G1 and return the name of the person in charge corresponding in column B.<br>If you&#8217;re using Excel 2021 or Office 365, you can use this formula:<br>=XLOOKUP(G1,A2:A11,B2:B11)<\/p>\n\n\n\n<figure class=\"wp-block-gallery has-nested-images columns-default is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\"><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"554\" height=\"318\" src=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/275.jpg\" alt=\"\" class=\"wp-image-4227\" srcset=\"https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/275.jpg 554w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/275-420x240.jpg 420w, https:\/\/owa.sodexo-cn.com\/blog\/wp-content\/uploads\/2022\/04\/275-315x180.jpg 315w\" sizes=\"auto, (max-width: 554px) 100vw, 554px\" \/><\/figure>\n\n\n\n<p>The first parameter is the content of the query, and the second parameter is the query area. You only need to select a column for the query area. The third argument is to return the contents of the column, again just by selecting a column.<br>The formula looks for the department specified in cell G1 in cell A2:A11 and returns the corresponding name in cell B2:B11.<\/p>\n\n\n\n<p>That\u2019s all for today, Have a great weekend!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hello everyone, today We continue to share some simple  &#8230; <a title=\"Simple and practical use of several function formulas II\" class=\"read-more\" href=\"https:\/\/owa.sodexo-cn.com\/blog\/?p=4229\" aria-label=\"\u9605\u8bfb Simple and practical use of several function formulas 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-4229","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"views":1497,"_links":{"self":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4229","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=4229"}],"version-history":[{"count":2,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4229\/revisions"}],"predecessor-version":[{"id":4231,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4229\/revisions\/4231"}],"wp:attachment":[{"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/owa.sodexo-cn.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}