{"id":19297,"date":"2023-12-05T13:20:29","date_gmt":"2023-12-05T13:20:29","guid":{"rendered":"https:\/\/masterskills.org\/blog\/cong-thuc-va-huong-dan-cach-thuc-hien-ham-sumif-trong-excel.html"},"modified":"2023-12-17T01:44:47","modified_gmt":"2023-12-17T01:44:47","slug":"cong-thuc-va-huong-dan-cach-thuc-hien-ham-sumif-trong-excel","status":"publish","type":"post","link":"https:\/\/masterskills.org\/blog\/cong-thuc-va-huong-dan-cach-thuc-hien-ham-sumif-trong-excel.html","title":{"rendered":"C\u00f4ng Th\u1ee9c V\u00e0 H\u01b0\u1edbng D\u1eabn C\u00e1ch Th\u1ef1c Hi\u1ec7n H\u00e0m SUMIF Trong Excel"},"content":{"rendered":"
\n

H\u00e0m sumif trong Excel<\/strong> l\u00e0 m\u1ed9t trong nh\u1eefng h\u00e0m c\u01a1 b\u1ea3n d\u00f9ng \u0111\u1ec3 t\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb trong ph\u1ea1m vi x\u00e1c \u0111\u1ecbnh. \u0110\u1ec3 bi\u1ebft c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m n\u00e0y, Masterskills\u0111\u00e3 t\u1ed5ng h\u1ee3p v\u00e0 chia s\u1ebb trong b\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y. H\u00e3y c\u00f9ng t\u00ecm hi\u1ec3u nh\u00e9!<\/span><\/p>\n

H\u00e0m sumif trong Excel c\u00f3 l\u1ee3i \u00edch g\u00ec trong vi\u1ec7c t\u00ednh to\u00e1n?<\/strong><\/h2>\n

H\u00e0m sumif trong Excel<\/strong> \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb \u0111\u00e3 th\u1ecfa m\u00e3n c\u00e1c \u0111i\u1ec1u ki\u1ec7n c\u1ee5 th\u1ec3 \u0111\u01b0\u1ee3c \u0111\u01b0a ra. Khi s\u1eed d\u1ee5ng h\u00e0m, c\u00f4ng vi\u1ec7c t\u00ednh to\u00e1n c\u1ee7a ng\u01b0\u1eddi d\u00f9ng s\u1ebd d\u1ec5 d\u00e0ng h\u01a1n. H\u00e0m sumif c\u00f2n c\u00f3 th\u1ec3 k\u1ebft h\u1ee3p v\u1edbi nh\u1eefng h\u00e0m kh\u00e1c \u0111\u1ec3 t\u00ecm ki\u1ebfm c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p.\u00a0\u00a0<\/span><\/p>\n

C\u00e1c c\u00f4ng th\u1ee9c v\u00e0 c\u00e1ch t\u00ednh h\u00e0m sumif trong Excel<\/strong><\/h2>\n

Sumif c\u00f3 r\u1ea5t nhi\u1ec1u c\u00f4ng th\u1ee9c xoay quanh n\u00f3. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 t\u1ed5ng h\u1ee3p c\u00e1c c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng ph\u1ed5 bi\u1ebfn nh\u1ea5t trong Excel m\u00e0 b\u1ea1n c\u1ea7n bi\u1ebft.<\/span><\/p>\n

C\u00f4ng th\u1ee9c v\u00e0 c\u00e1ch t\u00ednh h\u00e0m sumif trong Excel<\/strong><\/h3>\n

C\u00f4ng th\u1ee9c t\u00ednh h\u00e0m sumif c\u01a1 b\u1ea3n nh\u01b0 sau:<\/span><\/p>\n

= Sumif(range; criteria; sum_range)<\/span><\/p>\n

Trong \u0111\u00f3:<\/span><\/p>\n

    \n<\/ul>\n

    C\u00e1ch \u00e1p d\u1ee5ng<\/span> h\u00e0m sumif trong Excel <\/strong>\u0111\u01b0\u1ee3c t\u00ednh nh\u01b0 sau:<\/span><\/p>\n

      \n<\/ul>\n

      L\u01b0u \u00fd, b\u1ea1n c\u1ea7n c\u1ed1 \u0111\u1ecbnh v\u00f9ng ch\u1ee9a \u00f4 \u0111i\u1ec1u ki\u1ec7n t\u00ednh t\u1ed5ng (range) v\u00e0 v\u00f9ng c\u1ea7n t\u00ednh t\u1ed5ng (sum_range) b\u1eb1ng c\u00e1ch b\u00f4i \u0111en v\u00e0 ch\u1ecdn F4 \u0111\u1ec3 k\u1ebft qu\u1ea3 t\u00ednh \u0111\u01b0\u1ee3c ch\u00ednh x\u00e1c tuy\u1ec7t \u0111\u1ed1i.\u00a0 Ngo\u00e0i ra, v\u1edbi gi\u00e1 tr\u1ecb \u0111i\u1ec1u ki\u1ec7n t\u00ednh t\u1ed5ng (criteria) c\u1ea7n \u0111\u01b0\u1ee3c \u0111\u1eb7t trong d\u1ea5u nh\u00e1y k\u00e9p \u201c\u201d v\u00e0 ph\u1ea3i \u0111\u00fang t\u1eebng k\u00fd t\u1ef1 m\u1ed9t.<\/span><\/p>\n

      C\u00f4ng th\u1ee9c t\u00ednh h\u00e0m SUMIF trong Excel (Ngu\u1ed3n: Internet)<\/span><\/em><\/p>\n

      :\u00a0<\/span><\/p>\n

      H\u1ecdc marketing ra l\u00e0m g\u00ec? Top ngh\u1ec1 nghi\u1ec7p HOT nh\u1ea5t<\/span><\/p>\n

      T\u00ecm hi\u1ec3u v\u1ec1 ng\u00e0nh \u0111i\u1ec7n t\u1eed vi\u1ec5n th\u00f4ng l\u00e0 g\u00ec? Ra tr\u01b0\u1eddng l\u00e0m g\u00ec?<\/span><\/p>\n

      C\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 c\u1ee5 th\u1ec3 c\u00e1ch t\u00ednh h\u00e0m sumifs trong Excel<\/strong><\/h3>\n

      C\u00f4ng th\u1ee9c t\u00ednh sumifs trong excel \u0111\u01b0\u1ee3c th\u1ec3 hi\u1ec7n nh\u01b0 sau:<\/span><\/p>\n

      = Sumifs(sum_range; criteria_range; criteria;\u2026)<\/span><\/p>\n

      Trong \u0111\u00f3:<\/span><\/p>\n

        \n<\/ul>\n

        \u0110\u1ec3 d\u1ec5 h\u00ecnh dung, b\u1ea1n h\u00e3y theo d\u00f5i v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y:<\/span><\/p>\n

        B\u1ea1n c\u1ea7n t\u00ednh t\u1ed5ng th\u00e0nh ti\u1ec1n c\u00e1c lo\u1ea1i h\u00f3a ch\u1ea5t c\u00f3 s\u1ed1 l\u01b0\u1ee3ng l\u1edbn h\u01a1n 200 trong b\u1ea3ng.<\/span><\/p>\n

          \n<\/ul>\n

          \n

          C\u00f4ng th\u1ee9c h\u00e0m SUMIFS v\u00e0 v\u00ed d\u1ee5 c\u1ee5 th\u1ec3 (Ngu\u1ed3n: Internet)<\/span><\/em><\/p>\n

          C\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 c\u1ee5 th\u1ec3 c\u00e1ch t\u00ednh h\u00e0m sumif v\u00e0 c\u00e1c k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n<\/strong><\/h3>\n

          C\u00f4ng th\u1ee9c t\u00ednh h\u00e0m sumif v\u00e0 c\u00e1c k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n \u0111\u01b0\u1ee3c \u00e1p d\u1ee5ng khi t\u00ednh t\u1ed5ng c\u00e1c \u0111i\u1ec1u ki\u1ec7n \u00f4 d\u1ef1a tr\u00ean v\u0103n b\u1ea3n v\u00e0 b\u1ea1n mu\u1ed1n t\u00ednh t\u1ed5ng khi \u0111\u00e3 \u0111\u1ed1i chi\u1ebfu t\u1eebng ph\u1ea7n n\u1ed9i dung. C\u00f3 hai y\u1ebfu t\u1ed1 ng\u01b0\u1eddi d\u00f9ng c\u1ea7n quan t\u00e2m:<\/span><\/p>\n

            \n<\/ul>\n

            Gi\u1ea3 s\u1eed b\u1ea1n mu\u1ed1n t\u00ednh t\u1ed5ng s\u1ed1 ti\u1ec1n li\u00ean quan \u0111\u1ebfn c\u00e1c lo\u1ea1i chu\u1ed1i (bananas) th\u00ec c\u00f4ng th\u1ee9c s\u1ebd nh\u01b0 sau:\u00a0<\/span><\/p>\n

            = Sumif (A2:A8, \u201cbananas*\u201d,C2:C8) – h\u00e0m t\u00ednh d\u1ef1a tr\u00ean c\u00e1c v\u0103n b\u1ea3n k\u1ebft th\u00fac v\u1edbi d\u1ea5u hoa th\u1ecb.<\/span><\/p>\n

            Ho\u1eb7c n\u1ebfu b\u1ea1n mu\u1ed1n t\u00ednh t\u1ed5ng c\u1ee7a \u00f4 b\u1eaft \u0111\u1ea7u v\u00e0 \u00f4 k\u1ebft th\u00fac c\u1ee7a m\u1ed9t v\u0103n b\u1ea3n nh\u1ea5t \u0111\u1ecbnh th\u00ec h\u00e3y \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c: = sumif(A2: A8, “bananas*”, C2: C8) – khi \u0111\u00f3, gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 C2:C8 n\u1ebfu c\u00f3 \u00f4 t\u01b0\u01a1ng \u1ee9ng trong c\u1ed9t A b\u1eaft \u0111\u1ea7u b\u1eb1ng t\u1eeb \u201cbananas\u201d.<\/span><\/p>\n

            \n

            V\u00ed d\u1ee5 t\u00ednh h\u00e0m sumif trong Excel v\u00e0 k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n (Ngu\u1ed3n: Internet)<\/span><\/em><\/p>\n

            : <\/span>Designer l\u00e0 g\u00ec? M\u00f4 t\u1ea3 c\u00f4ng vi\u1ec7c v\u00e0 c\u01a1 h\u1ed9i ngh\u1ec1 nghi\u1ec7p c\u1ee7a Designer<\/span><\/p>\n

            C\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 c\u1ee5 th\u1ec3 c\u00e1ch t\u00ednh h\u00e0m sumif cho ng\u00e0y, th\u00e1ng<\/strong><\/h3>\n

            C\u00e1ch t\u00ednh h\u00e0m sumif cho ng\u00e0y th\u00e1ng c\u0169ng t\u01b0\u01a1ng t\u1ef1 nh\u01b0 \u0111\u1ed1i v\u1edbi v\u0103n b\u1ea3n ho\u1eb7c k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 b\u1ea3ng th\u1ed1ng k\u00ea c\u00e1c ti\u00eau ch\u00ed v\u00e0 c\u00f4ng th\u1ee9c v\u00ed d\u1ee5 cho b\u1ea1n \u0111\u1ecdc d\u1ec5 h\u00ecnh dung.<\/span><\/p>\n\n\n\n\n\n\n\n\n\n\n
            \n

            Ti\u00eau ch\u00ed<\/strong><\/p>\n<\/td>\n

            		\n

            V\u00ed d\u1ee5<\/strong><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb d\u1ef1a c\u1ee7a 1 ng\u00e0y th\u00e1ng nh\u1ea5t \u0111\u1ecbnh<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9,\u201d10\/29\/2014\u2033,C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb n\u1ebfu \u00f4 ng\u00e0y \u1edf \u00f4 t\u01b0\u01a1ng \u1ee9ng l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng v\u1edbi gi\u00e1 tr\u1ecb \u00f4 ng\u00e0y \u0111\u00e3 cho<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9,\u201d>=10\/29\/2014\u2033,C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng t\u1ea1i c\u00e1c \u00f4 ng\u00e0y \u1edf \u00f4 t\u01b0\u01a1ng \u1ee9ng l\u1edbn h\u01a1n \u00f4 ng\u00e0y \u1edf nh\u1eefng \u00f4 kh\u00e1c\u00a0<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9,\u201d>\u201d&F1,C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 d\u1ef1a tr\u00ean gi\u00e1 tr\u1ecb ng\u00e0y hi\u1ec7n t\u1ea1i<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9, TODAY(), C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb \u00f4 n\u1eb1m trong qu\u00e1 kh\u1ee9\u00a0<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9, \u201c<\u201c&TODAY(), C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb \u00f4 m\u00e0 n\u1eb1m trong t\u01b0\u01a1ng lai<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9, \u201c>\u201d&TODAY(), C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n

            \n

            T\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb c\u1ee7a ng\u00e0y x\u1ea3y ra tr\u01b0\u1edbc 1 tu\u1ea7n<\/span><\/p>\n<\/td>\n

            		\n

            =SUMIF(B2:B9, \u201c=\u201d&TODAY()+7, C2:C9)<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

            C\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 c\u1ee5 th\u1ec3 c\u00e1ch t\u00ednh h\u00e0m sumif trong t\u00ednh t\u1ed5ng c\u00e1c s\u1ed1 l\u1edbn nh\u1ea5t v\u00e0 nh\u1ecf nh\u1ea5t trong m\u1ed9t d\u1ea3i\u00a0<\/strong><\/h3>\n

            h\u00e0m sumif trong Excel<\/strong><\/p>\n<\/p>\n

            = SUM (LARGE (B1: B10, {1,2,3,4,5})) (\u00e1p d\u1ee5ng v\u1edbi t\u00ednh t\u1ed5ng 5 s\u1ed1 l\u1edbn nh\u1ea5t)<\/span><\/p>\n

            = SUM (SMALL (B1: B10, {1,2,3,4,5})) (\u00e1p d\u1ee5ng cho t\u00ednh t\u1ed5ng 5 s\u1ed1 nh\u1ecf nh\u1ea5t)<\/span><\/p>\n

            N\u1ebfu b\u1ea1n mu\u1ed1n th\u00eam nhi\u1ec1u s\u1ed1 c\u00f3 gi\u00e1 tr\u1ecb l\u1edbn nh\u1ecf t\u00f9y \u00fd th\u00ec b\u1ea1n n\u00ean s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m row ho\u1eb7c indirect v\u1edbi sum. Gi\u1ea3 s\u1eed b\u1ea1n c\u1ea7n t\u00ednh t\u1ed5ng c\u1ee7a 15 s\u1ed1 l\u1edbn nh\u1ea5t v\u00e0 nh\u1ecf nh\u1ea5t th\u00ec s\u1ebd c\u00f3 c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/span><\/p>\n

            = SUM (LARGE (B1: B50, ROW (INDIRECT (“1:15”)))) (\u00e1p d\u1ee5ng cho s\u1ed1 l\u1edbn nh\u1ea5t)<\/span><\/p>\n

            = SUM (SMALL (B1: B50, ROW (INDIRECT (“1:15”)))) (\u00e1p d\u1ee5ng cho s\u1ed1 nh\u1ecf nh\u1ea5t).<\/span><\/p>\n

            \u0110\u1ed1i v\u1edbi tr\u01b0\u1eddng h\u1ee3p t\u00ednh t\u1ed5ng c\u1ee7a bi\u1ebfn s\u1ed1 c\u00f3 gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t ho\u1eb7c nh\u1ecf nh\u1ea5t th\u00ec b\u1ea1n c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c sau:<\/span><\/p>\n

            = SUM (LARGE (B1: B50, ROW (INDIRECT (“1:” & E1)))) (\u00e1p d\u1ee5ng \u0111\u1ec3 t\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb h\u00e0ng \u0111\u1ea7u c\u00f3 ch\u1ee9a \u00f4 thay \u0111\u1ed5i).<\/span><\/p>\n

            = SUM (SMALL (B1: B50, ROW (INDIRECT (“1:” & E1)))) (\u00e1p d\u1ee5ng \u0111\u1ec3 t\u00ednh c\u00e1c gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t c\u00f3 ch\u1ee9a \u00f4 thay \u0111\u1ed5i)<\/span><\/p>\n

            :\u00a0<\/span><\/p>\n

            H\u1ecdc logistic ra l\u00e0m g\u00ec? L\u01b0\u01a1ng v\u00e0 c\u01a1 h\u1ed9i vi\u1ec7c l\u00e0m ra sao<\/span><\/p>\n

            H\u1ecdc kinh t\u1ebf ra l\u00e0m g\u00ec? C\u01a1 h\u1ed9i ph\u00e1t tri\u1ec3n v\u00e0 m\u1ee9c l\u01b0\u01a1ng ng\u00e0nh kinh t\u1ebf<\/span><\/p>\n

            Nh\u1eefng v\u1ea5n \u0111\u1ec1 th\u01b0\u1eddng m\u1eafc ph\u1ea3i khi d\u00f9ng h\u00e0m sumif trong Excel v\u00e0 c\u00e1ch kh\u1eafc ph\u1ee5c d\u1ec5 d\u00e0ng<\/strong><\/h2>\n

            Qu\u00e1 tr\u00ecnh s\u1eed d\u1ee5ng h\u00e0m sumif c\u00f3 th\u1ec3 khi\u1ebfn ng\u01b0\u1eddi d\u00f9ng \u0111\u1ed1i m\u1eb7t v\u1edbi m\u1ed9t s\u1ed1 v\u1ea5n \u0111\u1ec1. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 l\u01b0u \u00fd khi d\u00f9ng h\u00e0m m\u00e0 b\u1ea1n c\u1ea7n bi\u1ebft:<\/span><\/p>\n

              \n<\/ul>\n

              \n

              L\u1ed7i th\u01b0\u1eddng g\u1eb7p v\u00e0 c\u00e1ch kh\u1eafc ph\u1ee5c khi s\u1eed d\u1ee5ng h\u00e0m SUMIF (Ngu\u1ed3n: Internet)<\/span><\/em><\/p>\n

              Th\u00f4ng tin v\u1ec1 <\/span>h\u00e0m sumif trong Excel<\/strong> \u0111\u00e3 \u0111\u01b0\u1ee3c <\/span>Masterskills<\/span> chia s\u1ebb v\u1edbi qu\u00fd b\u1ea1n \u0111\u1ecdc. \u0110\u00e2y l\u00e0 h\u00e0m c\u01a1 b\u1ea3n t\u00ednh to\u00e1n trong Excel m\u00e0 ng\u01b0\u1eddi d\u00f9ng c\u1ea7n nh\u1edb. Ch\u00fac b\u1ea1n lu\u00f4n ho\u00e0n th\u00e0nh t\u1ed1t m\u1ecdi b\u00e0i b\u00e1o c\u00e1o, s\u1ed1 li\u1ec7u th\u1ed1ng k\u00ea khi s\u1eed d\u1ee5ng Excel.<\/span><\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"

              H\u00e0m sumif trong Excel<\/strong><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[431],"tags":[],"_links":{"self":[{"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/posts\/19297"}],"collection":[{"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/comments?post=19297"}],"version-history":[{"count":0,"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/posts\/19297\/revisions"}],"wp:attachment":[{"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/media?parent=19297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/categories?post=19297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/masterskills.org\/blog\/wp-json\/wp\/v2\/tags?post=19297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}