Hàm SUMIFS() tính tổng các ô trong vùng thỏa 1 hoặc nhiều điều kiện cho trước

Hôm nay Góc Kinh Nghiệm sẽ giới thiệu với bạn hàm SUMIFS() hàm này sẽ giúp bạn khác phục được nhược điểm của hàm SUMIF() trước đây. Hàm SUMIFS() có trong Excel 2007.
Để hiểu được bài viết này, bạn phải đọc bài SUMIF() trước.
Hàm SUMIFS() cho phép bạn tính tổng các ô trong vùng thỏa mãn 1 hoặc nhiều điều kiện cho trước. Trường hợp cho bài viết này là tính tổng tiền thâm niên của các nhân viên với điều kiện nhân viên đó làm trên 2 năm kinh nghiệm và phải là nam giới.


Cú pháp:
          =SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Trong dó
sum_range: bắt buộc, là một vùng các ô để tính tổng, ví dụ A1:A10. Các ô có giá trị chuỗi hoặc rỗng thì được bỏ qua.
criteria_range1: bắt buộc, là một vùng các ô để so sánh với điều kiện tính tổng, ví dụ B2:B6
criteria1: bắt buộc, là điều kiện để tính tổng, giá trị của criterial có thể là số, biểu thức, cột tham chiếu, chuỗi. Ví dụ: 45, “>45”, “C3”, “Góc Kinh Nghiệm” hoặc “45”
criteria_range2, criteria2, … là tùy chọn, không bắt buộc, có hay không cũng không sao. Excel cho phép tối đa là 127 cặp điều kiện criteria_range/criteria.
Ở bài viết này, Góc Kinh Nghiệm sử dụng lại dữ liệu của bài post SUMIF(), và thêm một cột giới tính. Dữ liệu như sau:

A B C D E
1 Họ tên Giới tính Số năm thâm niên Tiền thâm niên
2 Nguyễn Khánh Hưng Nam 3.5 17,500,000
3 Trần Xuần Vũ Nam 2.5 12,500,000
4 Đoàn Thanh Thúy Nữ 3.5 17,500,000
5 Nguyễn Văn Hòa Nam 3.5 17,500,000
6 Nguyễn Ngọc Thảo Vân Nữ 1.5 7,500,000
7 Tổng số thâm niên 14.5 47,500,000
8 Số tiền thâm niên cho mỗi năm 5,000,000

 

Như bạn đã thấy ở ô D7, kết quả là 47,500,000 là tổng tiền thâm niên cho nhân viên có trên 2 năm kinh nghiệm và giới tính là nam. Công thức tại ô D7 như sau:
          =SUMIFS(D2:D6,C2:C6,”>2“,B2:B6,”Nam“)
Ngoài ra các bạn có thể sử dụng wildcard như dấu ?, dấu * hoặc dấu ~ để kết hợp với điều kiện tính tổng. Dấu ? đại diện cho một ký tự duy nhất, còn dấu * đại diện cho n bất kỳ ký tự. Trong trường hợp điều kiện bạn muốn là dấu ? hoặc *, nó sẽ trùng với 2 trường hợp đầu, vì vậy bạn phải thêm dấu ~ trước 1 trong 2 ký tự đó.

So sánh hàm SUMIF() và SUMIFS()

SUMIF() SUMIFS()
Tính tổng chỉ với một điều kiện Tính tổng với 1 hoặc nhiều điều kiện
Các ô tính tổng (sum_range) là đối số cuối cùng trong hàm SUMIF() và là tùy chọn (không bắt buộc) Các ô tính tổng (sum_range) là đối số đầu tiên của hàm SUMIFS(), và là bắt buộc phải có

Góc Kinh Nghiệm chúc các bạn thành công! :razz:


31 comments

  1. Cho em hỏi về cách dò và cộng theo hàng ngang :
    ví dụ:

    Mã Số: A B A C A
    S.Lượng: 12 2 5 4 3
    Hàm nào tìm tổng số lượng của A = 20

    Thanks nhiều !

    1. A B C D E
      1 15 25 34 12 3
      2 ABCD DCBG CEAS AHGT 12FCVB

      giả sử mình có bảng dữ liệu sau: nếu muốn tính tổng dữ liệu A1:E1 với điều kiện A2:E2 có C(*C*) và không bắt đầu bằng một kí tự số thì làm như thế nào?

      1. bạn giúp mình với mình ko hiểu đẻ sao cho có thể chạy ngày được mỗi khi copy toàn phải sửa lại ngày thôi
        sumifs(A1:A100;B1:B100;”T”;C1:C100;”1″ VD HÀM NHƯ THẾ

        1. Bạn muốn copy thì phải dùng công thức tuyệt đối, nếu không các địa chỉ ô trong công thức sẽ thay đổi khi bạn dáng.

  2. XIN CHO HỎI NẾU MÌNH DÙNG HÀM sumifs TRONG BÀI THỐNG KÊ VỚI YÊU CẦU LÀ “Thống kê Thành Tiền theo Mã Vật tư và Mã kho trong 6 tháng đầu năm” THÌ MÌNH NÊN LÀM NHƯ THẾ NÀO? MONG CÁC BẠN CHỈ GIÚP!
    CHẦN THÀNH CẢM ƠN!

    1. Ma Kho Ma Vat Tu Ten Vat Tu So Luong Ngay xuat kho Don Gia Thanh Tien
      K02 VT001 Màn hình 23 30/04/2009 45 1035
      K03 VT001 Đĩa CD 12 12/05/2009 23 276
      K03 VT001 Màn hình 45 05/07/2009 5 225
      K01 VT001 Màn hình 30 10/10/2009 25 750
      K01 VT001 Màn hình 34 10/10/2009 25 850
      K02 VT002 Case 20 23/07/2009 15 300
      K02 VT002 Case 23 23/07/2009 15 345
      K01 VT002 Đĩa cứng 34 09/08/2009 112 3808
      K01 VT002 Đĩa cứng 12 01/10/2009 112 1344
      K03 VT002 DataCable 23 01/12/2009 3 69
      K03 VT002 DataCable 10 23/12/2009 3 30
      K03 VT003 DataCable 11 12/05/2009 3 33
      K03 VT003 Mouse 11 01/06/2009 5 55
      Thống kê Thành Tiền theo Mã Vật tư và Mã kho trong 6 tháng đầu năm

    2. Hàm sumifs() là để tính tổng các số lại thôi. Còn bạn muốn thống kê thì MS Excel hỗ trợ riêng các hàm phục vụ lĩnh vực thống kê bạn lên mạng tham khảo thêm về phần thống kê trong MS Excel nhé.

      1. cho mình hỏi, đề yêu cầu tính số lượng mặt hàng Màn hình trong tháng 7 thì phải làm thế nào? Vì bên sumifs không cho sử dụng thuật toán để tìm tháng 7!!!?
        giúp dùm với! thanks!

  3. mình làm theo như trên mà chẳng đựoc nhỉ, dù là làm í sỳ cả mẫu đó nhưng vẫn không cho kết quả là thế nào ? Why?

    1. mình cũng bị y như bạn , k hiểu sao excel của mình k dùm đc hàm sumif và sumifs :(

  4. Cho em hỏi: nếu như em có:
    A1: Sản phẩm A1B1 Qty 2 cái
    A2: sản phẩm A2B1 Qty 5 cai
    A3: sản phẩm A1B2 Qty 5 cai
    em muốn cộng sản phẩm nào có chữ B1 thì em làm như thế nào, cả nhà chỉ giùm em với.
    Em cảm ơn nhiều
    H.anh

    1. – Hàm sumifs() chỉ cộng số thôi.
      – Nếu số lượng của bạn là số thì bạn chỉ cần áp dụng hàm sumifs() ở trên là ra kết quả rồi.

    1. Bạn ơi, Góc Kinh Nghiệm chỉ dành thời gian cho việc trả lời câu hỏi liên quan đến bài post thôi.

  5. Nếu điều kiện của mình ở đây là ngày được không.
    Ví dụ: mình muốn tính tổng của những sản phẩm được bán sau ngày 20/10/14 thì có được không. Mình thử làm nhưng sao không được.

  6. Bài toán của mình đơn giản nhưng mimhf chưa giải đc. Mình có một bảng tính, trong đó mình muốn tính tổng của 1 cột mà trong đó có các ô có giá trị #N/A hoặc #VALUE thì làm ntn bạn nhỉ. Thank!

  7. Như trên nhưng dữ liệu là tên nằm hàng trên mà số hàng dưới thì sao
    Số xe 789 763
    Khối. 12. 10
    Số xe 724 789
    Khối. 10. 8

    Mình muốn tính tổng khối của từng xe.

  8. hi cac pro, giup dum minh voi. minh co 1 database nhu ben duoi, neu dung ham sumif thi ra ket qua, nhung dum ham sumifs tra ve #VALUE!, vay la bi lam sao? co cach nao khac phuc khong. Minh cam on moi nguoi nhieu.

    Ten Nhom SL ban thang 1
    tao qua 290 SUMIF($A$6:$A$50,A6,$L$6:$L$86)
    rau cai rau 160 SUMIFS($L$6:$L$86,$A$6:$A$50,A6)
    chanh qua 100
    oi qua 40
    tao qua 310

  9. cho mình hỏi: mình có một file dữ liệu về tổng số ngày nhận hàng, dãy các ngày tháng không giống nhau, có thể cùng một ngày nhận hàng lặp lại rất nhiều lần, nhưng theo nguyên tắc chỉ tính 1, vậy mình dùng hàm nào để tính vậy bạn? giúp mình với, mình cám ơn

  10. Mik đang có 1 vấn đề rắc rối bạn nào giúp mik với
    Cụ thể như thế này:
    Mik muốn tính tổng tiền chi trong tháng 5 với dk là chữ số đầu tiên của ô mã số phải = 5, thì lập công thức như thế nào vậy
    Mong mọi người giúp đỡ tại mik hơi bị tệ vấn đề này
    Thanks mọi người nhìu

    1. Dễ mà, bạn dùng dấu * như bài viết trên, và điều kiện của bạn sẽ là “5*”. Chúc bạn thành công.

  11. Bạn nào giúp mình với. Làm sao để tính tổng số bạn Nữ trong cột có cả Nam lẫn Nữ nhưng phải thỏa mãn điều kiện ở cột tuổi là sinh năm 1992?

Comments are closed.