Hàm SUMIFS, cách dùng hàm tính tổng nhiều điều kiện trong Excel

Kết hợp nhiều điều kiện khác nhau

Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF. Trong trường hợp bảng dữ liệu yêu cầu tính tổng kèm theo nhiều điều kiện, thì chúng ta phải sử dụng tới hàm SUMIFS. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUMIFS trong Excel.

Cú pháp hàm SUMIFS trong Excel

Cú pháp hàm SUMIFS trong Excel có dạng =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…).

Trong đó:

  • Sum_range: là các ô cần tính tổng trong bảng dữ liệu, những giá trị trống và giá trị text được bỏ qua, tham số bắt buộc phải có.
  • Criteria_range1: phạm vi cần được kiểm tra bằng điều kiện criteria1, là giá trị bắt buộc.
  • Criteria1: điều kiện áp dụng cho criteria_range1, có thể là một số, biểu thức, tham chiếu ô để xác định ô nào trong criteria_range1 sẽ được tính tổng, cũng là giá trị bắt buộc.
  • Criteria_range2,criteria2,…: tùy chọn các phạm vi và điều kiện bổ sung, tối đa 127 cặp criteria_range, criteria.

Lưu ý khi dùng hàm SUMIFS trong Excel

  • Mỗi ô trong vùng, phạm vi sum_range sẽ được cộng tổng khi thỏa mãn tất cả các điều kiện tương ứng đã xác định là đúng cho ô đó.
  • Các ô trong sum_range có chứa TRUE được coi là 1, các ô chứa FALSE được coi là 0.
  • Mỗi criteria_range phải có cùng kích thước vùng chọn giống với sum_range, criteria_range và sum_range phải có cùng số hàng và cùng số cột.
  • Criteria có thể dùng ký tự dấu hỏi chấm (?) thay cho ký tự đơn, dấu sao (*) thay cho một chuỗi. Nếu điều kiện là dấu hỏi chấm hay dấu sao thì phải nhập thêm dấu ~ ở phía trước, giá trị điều kiện là văn bản để trong dấu “.
  • Nhiều điều kiện được áp dụng bằng logic AND, tức là criteria1 VÀ criteria2, v.v...
  • Mỗi phạm vi bổ sung phải có cùng số lượng hàng và cột như tổng dải ô, nhưng các phạm vi không cần phải liền kề. Nếu bạn cung cấp các phạm vi không khớp, bạn sẽ gặp lỗi #VALUE.
  • Chuỗi văn bản trong tiêu chí phải được đặt trong dấu ngoặc kép (""), tức là "apple", ">32", "jap*"
  • Tham chiếu ô trong tiêu chí không được đặt trong dấu ngoặc kép, tức là "<"&A1
  • SUMIF và SUMIFS có thể xử lý các phạm vi, nhưng không thể xử lý các mảng. Điều này có nghĩa là bạn không thể sử dụng các hàm khác như YEAR trên phạm vi tiêu chí vì kết quả là một mảng. Nếu bạn cần chức năng này, hãy sử dụng hàm SUMPRODUCT.
  • Thứ tự của các đối số giữa các hàm SUMIFS và SUMIF là khác nhau. Phạm vi tổng là đối số đầu tiên trong SUMIFS, nhưng là đối số thứ ba trong SUMIF.

Hướng dẫn dùng hàm SUMIFS trong Excel

Sử dụng các ký tự đại diện

Các ký tự đại diện như ‘*’ và ‘?’ có thể được sử dụng trong đối số tiêu chí khi sử dụng hàm SUMIFS. Sử dụng các ký tự đại diện này sẽ giúp bạn tìm kiếm những kết quả trùng khớp tương tự nhưng không chính xác.

Dấu hoa thị (*) - Nó khớp với bất kỳ chuỗi ký tự nào, có thể được sử dụng sau, trước hoặc với những tiêu chí xung quanh để cho phép sử dụng một phần tiêu chí tìm kiếm.

Ví dụ, nếu bạn áp dụng các tiêu chí sau trong hàm SUMIFS:

  • N * - Ngụ ý tất cả các ô trong phạm vi bắt đầu bằng N
  • * N - Ngụ ý tất cả các ô trong phạm vi kết thúc bằng N
  • * N * - Các ô chứa N

Dấu chấm hỏi (?) - Phù hợp với bất kỳ ký tự đơn nào. Giả sử bạn áp dụng N?r làm tiêu chí. "?" ở đây sẽ thay thế cho một ký tự duy nhất. N?r sẽ tương ứng với North, Nor, v.v... Tuy nhiên, nó sẽ không tính đến Name.

Điều gì sẽ xảy ra nếu dữ liệu đã cho chứa dấu hoa thị hoặc dấu chấm hỏi thực sự?

Trong trường hợp này, bạn có thể sử dụng dấu ngã (~). Bạn cần gõ “~” trước dấu chấm hỏi trong trường hợp đó.

Sử dụng các Named Range với hàm SUMIFS

Named Range là tên mô tả của một tập hợp các ô hoặc dải ô trong một trang tính. Bạn có thể sử dụng các Named Range trong khi sử dụng hàm SUMIFS.

Ví dụ về hàm SUMIFS trong Excel

Chúng ta sẽ tính tổng với bảng dữ liệu dưới đây kèm theo một số điều kiện khác nhau.

Bảng dữ liệu

Ví dụ 1: Tính tổng sản phẩm mà nhân viên Hoài bán ra có đơn giá mặt hàng dưới 400.000đ.

Tại ô nhập kết quả chúng ta điền công thức =SUMIFS(D2:D7,C2:C7,"Hoài",E2:E7,"<400000") rồi nhấn Enter.

Trong đó:

  • D2:D7 là vùng cần tính tổng mặt hàng.
  • C2:C7 là vùng giá trị điều kiện tên nhân viên.
  • “Hoài” là điều kiện tên nhân viên cần tính số mặt hàng chứa trong vùng C2:C7.
  • E2:E7 là điều kiện mặt hàng.
  • “400.000” là điều kiện chứa mặt hàng trong vùng E2:E7.

Tính tổng đơn giá mặt hàng dưới giá trị nào đó

Kết quả ra tổng số lượng mặt hàng như hình dưới đây.

Kết quả số sản phẩm có đơn giá dưới giá trị nào đó

Ví dụ 2: Tính tổng số lượng mặt hàng bán ra của nhân viên Nga có số thứ tự < 5.

Tại ô nhập kết quả chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,"=Nga",A2:A7,"<5") rồi nhấn Enter. Tại tên nhân viên "Nga" nhập dấu = hoặc bỏ dấu = đều được.

Tính tổng sản phẩm của Nga có số thứ tự <5

Kết quả chúng ta được tổng các mặt hàng do nhân viên Nga bán có số thứ tự < 5 là 550 mặt hàng.

Tổng giá trị

Ví dụ 3: Tính tổng số lượng sản phẩm đã bán ra của nhân viên Nga, trừ sản phẩm khăn quàng cổ.

Tại ô nhập kết quả, chúng ta nhập công thức =SUMIFS(D2:D7,C2:C7,“Nga”,B2:B7,“<>khăn quàng cổ”). Trong đó dấu <> dùng để loại trừ một đối tượng nào đó trong vùng dữ liệu làm điều kiện.

Tính tổng đơn giá mặt hàng trừ giá trị nào đó

Kết quả cho ra tổng số sản phẩm như hình dưới đây.

Hàm SUMIFS

Trên đây là một số ví dụ về cách sử dụng hàm SUMIFS, tính tổng giá trị có nhiều điều kiện kết hợp. Chúng ta phải sắp xếp các vùng và điều kiện đi kèm hợp lý thì Excel mới nhận dạng được công thức hàm để tính.

Hạn chế của hàm SUMIFS

Hàm SUMIFS có một số hạn chế mà bạn nên biết:

  • Các điều kiện trong SUMIFS được kết hợp bởi logic AND. Nói cách khác, tất cả các điều kiện phải là TRUE để một ô được tính vào tổng. Để tính tổng các ô bằng logic OR, bạn có thể sử dụng một giải pháp thay thế trong các trường hợp đơn giản.
  • Hàm SUMIFS yêu cầu phạm vi thực tế cho tất cả các đối số phạm vi; bạn không thể sử dụng một mảng. Điều này có nghĩa là bạn không thể thực hiện những việc như trích xuất năm từ ngày bên trong hàm SUMIFS. Để thay đổi các giá trị xuất hiện trong đối số phạm vi trước khi áp dụng tiêu chí, hàm SUMPRODUCT là một giải pháp linh hoạt.
  • SUMIFS không phân biệt chữ hoa chữ thường. Để tính tổng các giá trị dựa trên điều kiện phân biệt chữ hoa chữ thường, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT với hàm EXACT.

Cách phổ biến nhất để giải quyết các giới hạn trên là sử dụng hàm SUMPRODUCT. Trong phiên bản Excel hiện tại, một tùy chọn khác là sử dụng các hàm BYROW và BYCOL mới hơn.

Chúc các bạn thực hiện thành công!

Thứ Bảy, 19/11/2022 12:00
4,323 👨 228.616
6 Bình luận
Sắp xếp theo
  • Thuy Anh Nguyen Ngoc
    Thuy Anh Nguyen Ngoc

    hàm này rất tiện, đỡ phải if lằng nhằng khi tính tổng

    Thích Phản hồi 11:49 29/03
    • Lan Vũ Vũ
      Lan Vũ Vũ

      hay quá!

      Thích Phản hồi 11:49 29/03
      • Pham Toan
        Pham Toan

        rất hay

        Thích Phản hồi 11:49 29/03
        • Pham Thuan
          Pham Thuan

          tuyệt vời

          Thích Phản hồi 11:49 29/03
          • Nguyen Anh Quan
            Nguyen Anh Quan

            Hướng dẫn rất chi tiết, mình đã hiểu cơ bản rùi cảm ơn bạn

            Thích Phản hồi 11:39 29/03
            • Trần Tiến
              Trần Tiến

              Cảm ơn bạn, có hướng dẫn sumif cho mình xin với

              Thích Phản hồi 11:39 29/03
              • Nguyễn Hương Trà
                Nguyễn Hương Trà

                Bạn đang tìm hàm sumif này phải ko?

                Thích Phản hồi 11:41 29/03
              • Nguyễn Tuyến
                Nguyễn Tuyến

                Hình như là bài hàm sumif này thì phải

                Thích Phản hồi 11:41 29/03