{"id":11207,"date":"2025-01-15T02:04:43","date_gmt":"2025-01-15T02:04:43","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/huong-dan-su-dung-ham-sumifs\/"},"modified":"2025-01-15T02:04:43","modified_gmt":"2025-01-15T02:04:43","slug":"huong-dan-su-dung-ham-sumifs","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/15\/huong-dan-su-dung-ham-sumifs\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng h\u00e0m SUMIFS"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><a href=\"http:\/\/blog.ezworkapp.com\/sumifs-ham-khong-thieu-trong-kinh-doanh.html\">H\u00e0m sum<\/a> (h\u00e0m t\u00ednh t\u1ed5ng) \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng r\u1ea5t nhi\u1ec1u trong Excel, t\u1eeb ng\u01b0\u1eddi d\u00f9ng b\u00ecnh th\u01b0\u1eddng cho \u0111\u1ebfn ng\u01b0\u1eddi c\u00f3 tr\u00ecnh \u0111\u1ed9\u00a0 cao. Trong b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta s\u1ebd n\u00f3i v\u1ec1 h\u00e0m sumifs v\u1edbi m\u1ed9t v\u00e0i t\u00ednh n\u0103ng n\u00e2ng cao h\u01a1n.<\/p>\n<p>Li\u1ec7u b\u1ea1n c\u00f3 th\u1ec3 t\u00ednh t\u1ed5ng c\u00e1c s\u1ed1 d\u1ef1a tr\u00ean \u0111i\u1ec1u ki\u1ec7n cho tr\u01b0\u1edbc? hay b\u1ea1n c\u00f3 th\u1ec3 t\u1ef1 th\u00eam c\u00e1c \u0111i\u1ec1u ki\u1ec7n v\u00e0 t\u00ednh t\u1ed5ng nh\u1eefng s\u1ed1 th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n \u0111\u00f3.<\/p>\n<p>V\u1edbi\u00a0 <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-sumif-trong-excel-vi-du-cong-thuc.html\"><strong>h\u00e0m SUMIFS<\/strong><\/a> trong Excel, m\u1ecdi th\u1ee9 \u0111\u1ec1u c\u00f3 th\u1ec3.<\/p>\n<p><strong>C\u00fa ph\u00e1p h\u00e0m SUMIFS<\/strong><\/p>\n<p>\u0110\u1ea7u ti\u00ean, h\u00e3y c\u00f9ng t\u00ecm hi\u1ec3u c\u00fa ph\u00e1p h\u00e0m m\u1ed9t c\u00e1ch r\u00f5 r\u00e0ng<\/p>\n<p><strong>SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], \u2026)<\/strong><\/p>\n<p><strong>Sum_range : <\/strong>Ph\u1ea1m vi \u00f4 c\u1ea7n t\u00ednh t\u1ed5ng.<\/p>\n<p><strong>Criteria_range1: <\/strong>Ph\u1ea1m vi \u0111\u01b0\u1ee3c ki\u1ec3m tra b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng\u00a0<em>Criteria1<\/em><strong>.<\/strong><\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-270.gif\" title=\"ex101_92019-nho-270\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><em>Criteria_range1<\/em>\u00a0v\u00e0\u00a0<em>Criteria1<\/em>\u00a0thi\u1ebft l\u1eadp m\u1ed9t c\u1eb7p t\u00ecm ki\u1ebfm, theo \u0111\u00f3 <em>Criteria1<\/em>\u00a0 s\u1ebd \u0111\u01b0\u1ee3c t\u00ecm ki\u1ebfm trong <em>Criteria_range1<\/em>\u00a0. \u00a0\u00a0Sau khi \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng c\u1ee7a <em>Criteria1<\/em>\u00a0trong\u00a0<em>Sum_range<\/em>\u00a0s\u1ebd \u0111\u01b0\u1ee3c c\u1ed9ng v\u00e0o<strong>.<\/strong><\/p>\n<p><strong>Criteria1<\/strong>:\u00a0 Ti\u00eau ch\u00ed d\u00f9ng \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh \u00f4 n\u00e0o trong\u00a0<strong><em>Criteria_range1<\/em><\/strong>\u00a0th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n.<\/p>\n<p><strong>Gi\u1ea3i th\u00edch h\u00e0m SUMIFS b\u1eb1ng v\u00ed d\u1ee5 <\/strong><\/p>\n<p>Trong v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y, d\u1eef li\u1ec7u v\u1ec1 k\u1ebft qu\u1ea3 b\u00e1n h\u00e0ng \u0111\u01b0\u1ee3c bi\u1ec3u di\u1ec5n theo c\u00e1c c\u1ed9t: V\u00f9ng mi\u1ec1n (Region), Nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng (Sale rep), S\u1ed1 h\u00e0ng b\u00e1n \u0111\u01b0\u1ee3c (Units sold) v\u00e0 Chi ph\u00ed (Cost).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9756 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/20-09-01.png\" width=\"548\" height=\"362\" title=\"20-09-01\"><\/p>\n<p>C\u00e2u h\u1ecfi 1:H\u00e3y t\u00ednh t\u1ed5ng chi ph\u00ed c\u1ee7a mi\u1ec1n \u0110\u00f4ng (East Region). \u0110\u1ec3 th\u1ef1c hi\u1ec7n b\u1ea1n c\u00f3 th\u1ec3 vi\u1ebft h\u00e0m nh\u01b0 sau:<\/p>\n<p>=SUMIFS(G:G,B:B,J5) = 6,002<\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<p>G:G l\u00e0 sum-range ( total cost)- c\u1ed9t ch\u1ee9a chi ph\u00ed c\u1ea7n t\u00ednh t\u1ed5ng<\/p>\n<p>B:B l\u00e0 Criteria range- ch\u1ee9a t\u00ean c\u00e1c v\u00f9ng mi\u1ec1n<\/p>\n<p>J5 l\u00e0 \u00f4 ch\u1ee9a ti\u00eau ch\u00ed \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh- trong v\u00ed d\u1ee5 n\u00e0y l\u00e0 : \u201cEast\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9757 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/20-09-02.png\" width=\"771\" height=\"394\" title=\"20-09-02\"><\/p>\n<p>C\u00e2u h\u1ecfi 2: H\u00e3y t\u00ednh t\u1ed5ng chi ph\u00ed c\u1ee7a V\u00f9ng \u00a0trung t\u00e2m \u201cCentral\u201d v\u1edbi s\u1ea3n ph\u1ea9m l\u00e0 \u201cPencil\u201d<\/p>\n<p>\u0110\u00e2y l\u00e0 c\u00f4ng th\u1ee9c cho c\u00e2u h\u1ecfi tr\u00ean:<\/p>\n<p>=SUMIFS(G:G,B:B,J5,D:D,K5)<\/p>\n<p>C\u00f3 th\u1ec3 nh\u1eadn th\u1ea5y r\u1eb1ng, c\u00f4ng th\u1ee9c tr\u00ean v\u1eabn \u0111\u01b0\u1ee3c gi\u1eef \u0111\u1ebfn J5, v\u00e0 ch\u1ec9 th\u00eam ph\u1ea7n ti\u00eau ch\u00ed th\u1ee9 2.<\/p>\n<p>Trong c\u00f4ng th\u1ee9c tr\u00ean, c\u1ed9t D:D \u0111\u01b0\u1ee3c l\u1ef1a ch\u1ecdn l\u00e0 criteria range th\u1ee9 hai b\u1edfi v\u00ec n\u00f3 ch\u1ee9a danh s\u00e1ch c\u00e1c s\u1ea3n ph\u1ea9m v\u00e0 \u00f4 ch\u1ee9a ti\u00eau ch\u00ed l\u00e0 K5 : \u201cPencil\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9758 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/20-09-03.png\" width=\"844\" height=\"326\" title=\"20-09-03\"><\/p>\n<p><strong>L\u01b0u \u00fd r\u1eb1ng<\/strong>: t\u1eeb ti\u00eau ch\u00ed th\u1ee9 hai b\u1ea1n kh\u00f4ng c\u1ea7n ph\u1ea3i ch\u1ecdn l\u1ea1i sum range<\/p>\n<p>C\u00e2u h\u1ecfi 3: H\u00e3y t\u00ednh t\u1ed5ng chi ph\u00ed c\u1ee7a mi\u1ec1n t\u00e2y \u201cwest\u201d, s\u1ea3n ph\u1ea9m l\u00e0 \u201c Binder\u201d v\u00e0 s\u1ed1 s\u1ea3n ph\u1ea9m \u0111\u00e3 b\u00e1n ph\u1ea3i l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 50.<\/p>\n<p>=SUMIFS(G:G,B:B,J5,D:D,K5,E:E,\u201d&gt;=50\u2033) \u00a0= 1,139<\/p>\n<p>C\u1ed9t E:E l\u00e0 criteria range th\u1ee9 ba- s\u1ed1 l\u01b0\u1ee3ng b\u00e1n ra cho t\u1eebng s\u1ea3n ph\u1ea9m<\/p>\n<p>V\u1edbi criteria 3, ta s\u1ebd bi\u1ec3u di\u1ec5n b\u1eb1ng k\u00ed hi\u1ec7u to\u00e1n h\u1ecdc \u0111\u1ec3 ch\u1ec9 ra r\u1eb1ng s\u1ed1 s\u1ea3n ph\u1ea9m \u0111\u00e3 b\u00e1n ph\u1ea3i l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 50.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9759 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/20-09-04.png\" width=\"821\" height=\"338\" title=\"20-09-04\"><\/p>\n<p><strong>Vi\u1ebft h\u00e0m SUMIFS v\u1edbi c\u00e1c k\u00fd t\u1ef1 \u0111\u1eb7c bi\u1ec7t<\/strong><\/p>\n<p>C\u00e2u h\u1ecfi 4: H\u00e3y t\u00ednh t\u1ed5ng chi ph\u00ed cho c\u00e1c s\u1ea3n ph\u1ea9m \u00a0Pen, Pencil &amp; Pen Set<br \/>\nTrong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 l\u00e0m theo hai c\u00e1ch<\/p>\n<p>C\u00e1ch 1: Vi\u1ebft ra t\u1eebng ti\u00eau ch\u00ed (criteria) nh\u01b0 c\u00f4ng th\u1ee9c tr\u00ean<\/p>\n<p>C\u00e1ch 2: S\u1eed d\u1ee5ng k\u00fd t\u1ef1 *,? . H\u00e3y xem c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y<\/p>\n<p>=SUMIFS(G2:G44,D2:D44,\u201dPe*\u201d) =8,350<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9760 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/20-09-05.png\" width=\"826\" height=\"356\" title=\"20-09-05\"><\/p>\n<p>G2: G44: sum range- Total Cost<\/p>\n<p>D2:D44: criteria range- ch\u1ee9a danh s\u00e1ch c\u00e1c s\u1ea3n ph\u1ea9m<\/p>\n<p>\u201cPe*\u201d: n\u00f3 c\u00f3 ngh\u0129a l\u00e0 khi b\u1ea1n t\u00ecm ki\u1ebfm t\u00ean s\u1ea3n ph\u1ea9m, ch\u00fang s\u1ebd b\u1eaft \u0111\u1ea7u b\u1eb1ng ch\u1eef \u201cPe\u201d. B\u1edfi ch\u00fang ta \u0111ang t\u00ednh cho \u201cPencil\u201d, \u201cPen\u201d &amp; \u201cPen Stand\u201d.<\/p>\n<p>\u201c*\u201d c\u00f3 ngh\u0129a l\u00e0 t\u1ea5t c\u1ea3 c\u00e1c ch\u1eef c\u00e1i \u0111\u1eb1ng sau \u201cPe\u201d<\/p>\n<p><strong>Th\u00f4ng tin b\u1ed5 \u00edch<\/strong><\/p>\n<ul>\n<li>V\u1edbi h\u00e0m SUMIFS, b\u1ea1n c\u00f3 th\u1ec3 th\u00eam \u0111\u1ebfn 127 \u0111i\u1ec1u ki\u1ec7n hay ti\u00eau ch\u00ed m\u1ed9t l\u1ea7n. Tuy nhi\u00ean,c\u00e0ng nhi\u1ec1u ti\u00eau ch\u00ed c\u00e0ng d\u1eabn \u0111\u1ebfn sai s\u00f3t, v\u00ec v\u1eady h\u00e3y c\u1ed1 g\u1eafng vi\u1ebft ng\u1eafn g\u1ecdn.<\/li>\n<li>N\u1ebfu c\u00f3 b\u1ea5t k\u00ec \u00f4 tr\u1ed1ng n\u00e0o trong c\u00e1c d\u00e3y ti\u00eau ch\u00ed (criteria range), Excel s\u1ebd cho k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 \u201ckh\u00f4ng th\u1ef1c hi\u1ec7n \u0111\u01b0\u1ee3c\u201d. V\u00ec v\u1eady, h\u00e3y ki\u1ec3m tra v\u00e0 \u0111i\u1ec1n \u0111\u1ea7y \u0111\u1ee7 v\u00e0o c\u00e1c d\u00e3y, \u0111\u1eb7c bi\u1ec7t criteria range \u0111\u1ec3 \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 ch\u00ednh x\u00e1c.<\/li>\n<li>Ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c k\u00ed t\u1ef1 \u0111\u1eb7c bi\u1ec7t trong c\u00f4ng th\u1ee9c SUMIFS. V\u00ed d\u1ee5: \u201c?\u201d l\u00e0 t\u00ecm ki\u1ebfm m\u1ed9t ch\u1eef c\u00e1i v\u00e0 \u201c*\u201d l\u00e0 ph\u1ea7n c\u00f2n l\u1ea1i sau c\u00e1c ch\u1eef c\u00e1i c\u1ee5 th\u1ec3 n\u00e0o \u0111\u00f3.<\/li>\n<\/ul>\n<p><strong>K\u1ebft lu\u1eadn:<\/strong><\/p>\n<p>H\u00e0m SUMIFS l\u00e0 m\u1ed9t trong nh\u1eefng h\u00e0m \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng nhi\u1ec1u nh\u1ea5t trong ph\u00e2n t\u00edch, l\u1eadp b\u00e1o c\u00e1o\u2026 V\u00ec v\u1eady, h\u00e3y d\u00e0nh th\u1eddi gian \u0111\u1ec3 h\u1ecdc v\u00e0 th\u1ef1c h\u00e0nh v\u1edbi n\u00f3.<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-270.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":11208,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-11207","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=11207"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/11207\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/11208"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=11207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=11207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=11207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}