{"id":5152,"date":"2024-12-09T11:10:20","date_gmt":"2024-12-09T11:10:20","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-sumif-dang-mang\/"},"modified":"2024-12-09T11:10:20","modified_gmt":"2024-12-09T11:10:20","slug":"4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-sumif-dang-mang","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-sumif-dang-mang\/","title":{"rendered":"4 ph\u01b0\u01a1ng ph\u00e1p \u0111\u1ebfm nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n d\u1ea1ng ho\u1eb7c \u2013 H\u00e0m SUM+IF d\u1ea1ng m\u1ea3ng"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Vi\u1ec7c\u00a0\u0111\u1ebfm nh\u1eefng d\u1eef li\u1ec7u th\u1ecfa m\u00e3n theo m\u1ed9t v\u00e0i\u00a0\u0111i\u1ec1u ki\u1ec7n nh\u1ea5t\u00a0\u0111\u1ecbnh trong\u00a0Excel l\u00e0 c\u00f4ng vi\u1ec7c ch\u00fang ta th\u01b0\u1eddng g\u1eb7p ph\u1ea3i khi l\u00e0m vi\u1ec7c. Nh\u01b0ng vi\u1ec7c \u201ct\u1eadp\u00a0\u0111\u1ebfm\u201d kh\u00f4ng ph\u1ea3i l\u00fac n\u00e0o c\u0169ng d\u1ec5 l\u00e0m,\u00a0\u0111\u1eb7c bi\u1ec7t khi ph\u1ea3i\u00a0\u0111\u1ebfm theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n. Trong ch\u00f9m b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc\u00a0Excel Online s\u1ebd gi\u1edbi thi\u1ec7u t\u1edbi c\u00e1c b\u1ea1n c\u00e1ch x\u1eed l\u00fd v\u1edbi vi\u1ec7c\u00a0\u0111\u1ebfm theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n d\u1ea1ng HO\u1eb6C.\u00a0\u0110\u00e2y l\u00e0 d\u1ea1ng\u00a0\u0111i\u1ec1u ki\u1ec7n kh\u00f3 nh\u1ea5t trong vi\u1ec7c\u00a0\u0111\u1ebfm. Ch\u00fang ta s\u1ebd gi\u1ea3i quy\u1ebft y\u00eau c\u1ea7u n\u00e0y kh\u00f4ng ch\u1ec9 v\u1edbi 1 c\u00e1ch m\u00e0 c\u00f3 t\u1edbi 4 c\u00e1ch kh\u00e1c nhau. H\u00e3y c\u00f9ng t\u00ecm hi\u1ec3u c\u00e1c c\u00e1ch n\u00e0y nh\u00e9:<\/p>\n<p><strong>Y\u00eau c\u1ea7u c\u1ee7a ch\u00fang ta nh\u01b0 sau<\/strong>:<\/p>\n<p>Cho b\u1ea3ng d\u1eef li\u1ec7u:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14788\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-count-multi-or-criteria-01-1.png\" width=\"418\" height=\"373\" title=\"heo-count-multi-or-criteria-01-1\"><\/p>\n<p>\u0110\u1ebfm S\u1ed1 ng\u01b0\u1eddi c\u00f3 gi\u1edbi t\u00ednh l\u00e0 nam c\u00f3 tu\u1ed5i t\u1eeb 20 tr\u1edf l\u00ean, ho\u1eb7c c\u00f3 s\u1ed1 th\u1ebb l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 50<\/p>\n<h2>Ph\u01b0\u01a1ng ph\u00e1p th\u1ee9 3: S\u1eed d\u00f9ng h\u00e0m\u00a0SUM +\u00a0IF d\u1ea1ng m\u1ea3ng<\/h2>\n<p>Trong y\u00eau c\u1ea7u tr\u00ean c\u1ee7a\u00a0\u0111\u1ec1 b\u00e0i v\u00e0 d\u1ef1a v\u00e0o nh\u1eefng n\u1ed9i dung c\u1ee7a b\u00e0i h\u1ecdc v\u1ec1 h\u00e0m DCOUNTA, ch\u00fang ta c\u00f3 th\u1ec3 c\u00f3 th\u00eam 1 h\u01b0\u1edbng gi\u1ea3i quy\u1ebft nh\u01b0 sau:<\/p>\n<ul>\n<li>Theo\u00a0\u0111i\u1ec1u ki\u1ec7n\u00a0\u0111\u00e3 l\u1eadp trong h\u00e0m DCOUNTA, ch\u00fang ta c\u00f3 th\u1ec3 di\u1ec5n gi\u1ea3i n\u00f3 theo h\u01b0\u1edbng logic nh\u01b0 sau:<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14868\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-count-multi-or-criteria-02.png\" width=\"278\" height=\"105\" title=\"heo-count-multi-or-criteria-02\"><\/p>\n<p>1. Gi\u1edbi t\u00ednh l\u00e0 v\u00f9ng\u00a0B2:B13, gi\u1edbi t\u00ednh = Nam<\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-172.gif\" title=\"vba101_92019-nho-172\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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\/2024\/12\/ex101_92019-nho-437.gif\" title=\"ex101_92019-nho-437\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>2. Tu\u1ed5i l\u00e0 v\u00f9ng C2:C13, tu\u1ed5i &gt;=20<\/p>\n<p>3. S\u1ed1 th\u1ebb l\u00e0 v\u00f9ng D2:D13, s\u1ed1 th\u1ebb &gt;=50<\/p>\n<p>D\u1ea1ng\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0 Ho\u1eb7c\u00a0\u0111\u1ed3ng th\u1eddi c\u1ea3 3\u00a0\u0111i\u1ec1u ki\u1ec7n n\u00ean ta c\u00f3 th\u1ec3 vi\u1ebft\u00a0\u0111i\u1ec1u ki\u1ec7n nh\u01b0 sau:<\/p>\n<blockquote>\n<p>(B2:B13=F4)+(C2:C13&gt;=20)+(D2:D13&gt;=50)<\/p>\n<\/blockquote>\n<p>\u0110\u00e2y l\u00e0 c\u00e1ch vi\u1ebft d\u1ea1ng m\u1ea3ng, x\u00e9t m\u1ed7i ph\u1ea7n t\u1eed trong m\u1ea3ng. N\u1ebfu nh\u01b0 logic tr\u00ean l\u00e0\u00a0\u0111\u00fang,\u00a0m\u1ed7i ph\u1ea7n t\u1eed\u00a0\u0111\u00fang s\u1ebd\u00a0\u0111\u1ebfm l\u00e0 1. Nh\u01b0 v\u1eady ch\u00fang ta k\u1ebft h\u1ee3p th\u00eam h\u00e0m\u00a0IF nh\u01b0 sau:<\/p>\n<blockquote>\n<p>IF((B2:B13=F4)+(C2:C13&gt;=20)+(D2:D13&gt;=50),1)<\/p>\n<\/blockquote>\n<p>Ch\u1ec9 c\u1ea7n x\u00e9t m\u1ec7nh\u00a0\u0111\u1ec1\u00a0\u0111\u00fang, kh\u00f4ng c\u1ea7n x\u00e9t m\u1ec7nh\u00a0\u0111\u1ec1 sai (n\u1ebfu sai th\u00ec b\u1eb1ng 0 ho\u1eb7c b\u1ecf qua)<\/p>\n<p>T\u1ed5ng c\u00e1c k\u1ebft qu\u1ea3 th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n\u00a0\u0111\u01b0\u1ee3c c\u1ed9ng l\u1ea1i b\u1eb1ng h\u00e0m SUM:<\/p>\n<blockquote>\n<p>=SUM(IF((B2:B13=F4)+(C2:C13&gt;=20)+(D2:D13&gt;=50),1))<\/p>\n<\/blockquote>\n<p>C\u00f4ng th\u1ee9c tr\u00ean\u00a0\u00e1p d\u1ee5ng cho d\u1ea1ng m\u1ea3ng, v\u00ec v\u1eady k\u1ebft th\u00fac b\u1eb1ng t\u1ed5 h\u1ee3p ph\u00edm Ctrl+Shift+Enter<\/p>\n<p>K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14938\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-count-multi-or-criteria-05.png\" width=\"1146\" height=\"390\" title=\"heo-count-multi-or-criteria-05\"><\/p>\n<p>Nh\u01b0 v\u1eady c\u00e1ch s\u1eed d\u1ee5ng SUM +\u00a0IF trong c\u00f4ng th\u1ee9c m\u1ea3ng c\u0169ng ra k\u1ebft qu\u1ea3\u00a0\u0111\u00fang.<\/p>\n<p><strong>\u01afu\u00a0\u0111i\u1ec3m<\/strong><\/p>\n<ul>\n<li>Logic v\u00e0 c\u00e1ch vi\u1ebft\u00a0\u0111\u1ec1u r\u00f5 r\u00e0ng, d\u1ec5 hi\u1ec3u<\/li>\n<li>S\u1eed d\u1ee5ng nh\u1eefng c\u00f4ng th\u1ee9c ph\u1ed5 bi\u1ebfn nh\u1ea5t: SUM,\u00a0IF<\/li>\n<\/ul>\n<p><strong>Nh\u01b0\u1ee3c\u00a0\u0111i\u1ec3m<\/strong><\/p>\n<ul>\n<li>S\u1eed d\u1ee5ng d\u1ea1ng c\u00f4ng th\u1ee9c m\u1ea3ng kh\u00f3 vi\u1ebft h\u01a1n c\u00f4ng th\u1ee9c b\u00ecnh th\u01b0\u1eddng<\/li>\n<\/ul>\n<p><strong>B\u00e0i sau<\/strong>: C\u00e1ch th\u1ee9 4: S\u1eed d\u1ee5ng h\u00e0m SUMPRODUCT<\/p>\n<p>\u2014\u2014\u2014<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng t\u1ed1t Excel v\u00e0o trong c\u00f4ng vi\u1ec7c, ch\u00fang ta kh\u00f4ng ch\u1ec9 n\u1eafm v\u1eefng \u0111\u01b0\u1ee3c c\u00e1c h\u00e0m m\u00e0 c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u1ea3 c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel. Nh\u1eefng h\u00e0m n\u00e2ng cao gi\u00fap \u00e1p d\u1ee5ng t\u1ed1t v\u00e0o c\u00f4ng vi\u1ec7c nh\u01b0 SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH\u00a0 \u2026 Nh\u1eefng c\u00f4ng c\u1ee5 th\u01b0\u1eddng s\u1eed d\u1ee5ng l\u00e0 Data validation, Conditional formatting, Pivot table \u2026<\/p>\n<p>To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2mWmtzP\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a>\u00a0c\u1ee7a H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc. Chi ti\u1ebft xem t\u1ea1i: <a href=\"https:\/\/hocexcel.online\" target=\"_blank\">HocExcel.Online<\/a><\/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\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-172.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5153,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5152","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5152","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=5152"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5152\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5153"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}