{"id":5191,"date":"2024-12-09T17:03:32","date_gmt":"2024-12-09T17:03:32","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs\/"},"modified":"2024-12-09T17:03:32","modified_gmt":"2024-12-09T17:03:32","slug":"4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs\/","title":{"rendered":"4 ph\u01b0\u01a1ng ph\u00e1p \u0111\u1ebfm nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n d\u1ea1ng ho\u1eb7c \u2013 H\u00e0m COUNTIFS"},"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-3.png\" width=\"418\" height=\"373\" title=\"heo-count-multi-or-criteria-01-3\"><\/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<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-675722e374512\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs.html#Phuong_phap_thu_1_Su_dung_ham_COUNTIFS\" title=\"Ph\u01b0\u01a1ng ph\u00e1p th\u1ee9 1: S\u1eed d\u1ee5ng h\u00e0m COUNTIFS\">Ph\u01b0\u01a1ng ph\u00e1p th\u1ee9 1: S\u1eed d\u1ee5ng h\u00e0m COUNTIFS<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs.html#1_Phan_tich_yeu_cau\" title=\"1. Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\">1. Ph\u00e2n t\u00edch y\u00eau c\u1ea7u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs.html#2_Xay_dung_cong_thuc_tinh\" title=\"2. X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh\">2. X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-countifs.html#Danh_gia\" title=\"\u0110\u00e1nh gi\u00e1\">\u0110\u00e1nh gi\u00e1<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Phuong_phap_thu_1_Su_dung_ham_COUNTIFS\"><\/span>Ph\u01b0\u01a1ng ph\u00e1p th\u1ee9 1: S\u1eed d\u1ee5ng h\u00e0m COUNTIFS<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Nh\u1eafc t\u1edbi h\u00e0m\u00a0\u0111\u1ebfm trong\u00a0Excel, ch\u00fang ta ngh\u0129 ngay t\u1edbi c\u00e1c h\u00e0m COUNT, COUNTA, COUNTIF, COUNTIFS. C\u1ee5 th\u1ec3 h\u01a1n trong y\u00eau c\u1ea7u l\u00e0\u00a0\u0111\u1ebfm theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n. V\u00ec c\u00f3 \u201c\u0110i\u1ec1u ki\u1ec7n\u201d n\u00ean ch\u00fang ta s\u1ebd c\u00f3\u00a0IF trong h\u00e0m\u00a0\u0111\u1ebfm, \u201cNhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n\u201d n\u00ean s\u1ebd c\u00f3\u00a0IF s\u1ed1 nhi\u1ec1u (th\u00eam ch\u1eef S) =&gt; H\u00e0m s\u1eed d\u1ee5ng l\u00e0 <strong>COUNTIFS<\/strong><\/p>\n<p><strong><em>L\u01b0u\u00a0\u00fd: H\u00e0m COUNTIFS kh\u00f4ng d\u00f9ng\u00a0\u0111\u01b0\u1ee3c\u00a0\u1edf c\u00e1c phi\u00ean b\u1ea3n Microsoft\u00a0Office th\u1ea5p h\u01a1n 2007<\/em><\/strong><\/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-173.gif\" title=\"vba101_92019-nho-173\"><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-441.gif\" title=\"ex101_92019-nho-441\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"1_Phan_tich_yeu_cau\"><\/span>1. Ph\u00e2n t\u00edch y\u00eau c\u1ea7u<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Y\u00eau c\u1ea7u l\u00e0: \u201c<strong>\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<\/strong>\u201c.\u00a0\u0110\u00e2y l\u00e0 y\u00eau c\u1ea7u r\u1ea5t r\u00f5 r\u00e0ng v\u1ec1 ngh\u0129a ti\u1ebfng vi\u1ec7t, nh\u01b0ng\u00a0\u0111\u1ec3\u00a0Excel hi\u1ec3u\u00a0\u0111\u01b0\u1ee3c y\u00eau c\u1ea7u n\u00e0y\u00a0\u0111\u1ec3 t\u00ednh to\u00e1n\u00a0\u0111\u01b0\u1ee3c th\u00ec ch\u00fang ta c\u1ea7n ph\u1ea3i ph\u00e2n t\u00edch c\u1ee5 th\u1ec3 h\u01a1n:<\/p>\n<ul>\n<li>\u0110\u1ed1i t\u01b0\u1ee3ng c\u00f3 li\u00ean quan: c\u1ed9t <strong>Gi\u1edbi t\u00ednh<\/strong>, c\u1ed9t <strong>Tu\u1ed5i<\/strong>, c\u1ed9t <strong>S\u1ed1 th\u1ebb<\/strong><\/li>\n<li>\u0110i\u1ec1u ki\u1ec7n c\u1ee5 th\u1ec3: Gi\u1edbi t\u00ednh = Nam, Tu\u1ed5i &gt;=20, S\u1ed1 th\u1ebb &gt;=50<\/li>\n<li>M\u1ed1i li\u00ean quan gi\u1eefa c\u00e1c\u00a0\u0111i\u1ec1u ki\u1ec7n l\u00e0 g\u00ec?<\/li>\n<\/ul>\n<p>N\u1ebfu kh\u00f4ng c\u00f3 m\u1ed1i li\u00ean quan th\u00ec s\u1ebd\u00a0\u0111\u1ebfm ri\u00eang l\u1ebb t\u1eebng\u00a0\u0111i\u1ec1u ki\u1ec7n r\u1ed3i c\u1ed9ng l\u1ea1i. Nh\u01b0ng\u00a0\u1edf\u00a0\u0111\u00e2y c\u00e1c\u00a0\u0111i\u1ec1u ki\u1ec7n c\u00f3 m\u1ed1i quan h\u1ec7 v\u1edbi nhau<\/p>\n<p>Gi\u1edbi t\u00ednh l\u00e0 nam c\u00f3 tu\u1ed5i t\u1eeb 20 tr\u1edf l\u00ean: Trong n\u00e0y bao h\u00e0m\u00a0\u0111i\u1ec1u ki\u1ec7n \u201cV\u00c0\u201d (AND)<\/p>\n<p>Ho\u1eb7c c\u00f3 s\u1ed1 th\u1ebb l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 50: Trong n\u00e0y bao h\u00e0m\u00a0\u0111i\u1ec1u ki\u1ec7n \u201cHO\u1eb6C\u201d (OR)<\/p>\n<p>C\u1ee5 th\u1ec3 nh\u01b0 sau:<\/p>\n<ul>\n<li>Y\u00eau c\u1ea7u 1: Gi\u1edbi t\u00ednh l\u00e0 n\u0103m v\u00e0 c\u00f3 tu\u1ed5i t\u1eeb 20 tr\u1edf l\u00ean<\/li>\n<li>Y\u00eau c\u1ea7u 2: Gi\u1edbi t\u00ednh l\u00e0 nam v\u00e0 c\u00f3 s\u1ed1 th\u1ebb l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 50<\/li>\n<li>Y\u00eau c\u1ea7u 1 ho\u1eb7c y\u00eau c\u1ea7u 2\u00a0\u0111\u1ec1u\u00a0\u0111\u00fang<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"2_Xay_dung_cong_thuc_tinh\"><\/span>2. X\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>X\u00e9t y\u00eau c\u1ea7u 1: g\u1ed3m 2\u00a0\u0111i\u1ec1u ki\u1ec7n<\/p>\n<p>C\u00e1c \u0111\u1ed1i t\u01b0\u1ee3ng trong h\u00e0m countifs bao g\u1ed3m:<\/p>\n<ul>\n<li><strong>Criteria_range1<\/strong>: V\u00f9ng ch\u1ee9a \u0111i\u1ec1u ki\u1ec7n th\u1ee9 1, c\u1ee5 th\u1ec3 l\u00e0 c\u1ed9t Gi\u1edbi t\u00ednh <strong>B2:B13<\/strong><\/li>\n<li><strong>Criteria1<\/strong>:\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 1 (n\u1eb1m trong v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 1), c\u1ee5 th\u1ec3 l\u00e0 gi\u1edbi t\u00ednh <strong>Nam<\/strong><\/li>\n<li><strong>Criteria_range2<\/strong>: V\u00f9ng ch\u1ee9a\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, c\u1ee5 th\u1ec3 l\u00e0 c\u1ed9t Tu\u1ed5i <strong>C2:C13<\/strong> (y\u00eau c\u1ea7u l\u00e0 c\u00e1c v\u00f9ng criteria_range ph\u1ea3i c\u00f3\u00a0\u0111\u1ed9 l\u1edbn b\u1eb1ng nhau)<\/li>\n<li><strong>Criteria2<\/strong>:\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 2 (n\u1eb1m trong v\u00f9ng\u00a0\u0111i\u1ec1u ki\u1ec7n th\u1ee9 2), c\u1ee5 th\u1ec3 l\u00e0 tu\u1ed5i <strong>&gt;=20<\/strong><\/li>\n<\/ul>\n<p>C\u00e1ch vi\u1ebft h\u00e0m Countifs nh\u01b0 sau:<\/p>\n<blockquote>\n<p>=COUNTIFS(B2:B13,\u201dNam\u201d,C2:C13,\u201d&gt;=\u201d&amp;20)<\/p>\n<\/blockquote>\n<p>T\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady v\u1edbi y\u00eau c\u1ea7u 2 ch\u00fang ta c\u00f3 c\u00f4ng th\u1ee9c:<\/p>\n<blockquote>\n<p>=COUNTIFS(B2:B13,\u201dNam\u201d,D2:D13,\u201d&gt;=\u201d&amp;50)<\/p>\n<\/blockquote>\n<p>V\u00ec 2\u00a0\u0111i\u1ec1u ki\u1ec7n n\u00e0y l\u00e0 d\u1ea1ng Ho\u1eb7c: ch\u1ec9 c\u1ea7n\u00a0\u0111\u00fang 1 trong 2 tr\u01b0\u1eddng h\u1ee3p\u00a0th\u00ec\u00a0\u0111\u1ec1u\u00a0\u0111\u01b0\u1ee3c t\u00ednh n\u00ean c\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t l\u00e0:<\/p>\n<p>=COUNTIFS(B2:B13,\u201dNam\u201d,C2:C13,\u201d&gt;=\u201d&amp;20)+COUNTIFS(B2:B13,\u201dNam\u201d,D2:D13,\u201d&gt;=\u201d&amp;50)<\/p>\n<p>K\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14874\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-count-multi-or-criteria-04.png\" width=\"1084\" height=\"391\" title=\"heo-count-multi-or-criteria-04\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Danh_gia\"><\/span>\u0110\u00e1nh gi\u00e1<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>\u01afu \u0111i\u1ec3m:<\/strong><\/p>\n<ul>\n<li>Kh\u00f4ng c\u1ea7n t\u1edbi c\u00f4ng th\u1ee9c m\u1ea3ng<\/li>\n<li>Ph\u00e2n r\u00f5 \u0111\u01b0\u1ee3c t\u00ednh ch\u1ea5t, y\u00eau c\u1ea7u c\u1ee7a b\u00e0i to\u00e1n<\/li>\n<li>D\u1ec5 th\u1ef1c hi\u1ec7n<\/li>\n<\/ul>\n<p><strong>Nh\u01b0\u1ee3c \u0111i\u1ec3m<\/strong><\/p>\n<ul>\n<li>C\u1ea7n t\u1edbi 2 l\u1ea7n s\u1eed d\u1ee5ng h\u00e0m =&gt; C\u00f4ng th\u1ee9c d\u00e0i<\/li>\n<\/ul>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t c\u00f9ng H\u1ecdc Excel Online!<\/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-173.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5192,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5191","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\/5191","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=5191"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5191\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5192"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}