4 hàm tính tổng trong Excel có điều kiện | Nhất định phải biết

Giới thiệu 4 hàm tính tổng trong Excel có điều kiện

Các hàm tính tổng có điều kiện trong Excel được sử dụng nhiều nhất là hàm SUMIF, SUMIFS, SUMPRODUCt, SUBTOTAL. Tùy từng trường hợp cụ thể, mỗi hàm sẽ giúp bạn tính tổng có điều kiện thuận tiện và nhanh chóng nhất khi bảng nhiều dữ liệu.

Hàm SUMIF là hàm tính tổng có điều kiện cho phép bạn tính tổng một vùng nào đó trong bảng tính Excel theo một điều kiện cho trước.

Cú pháp: =SUMIF(range, criteria,sum_range)

Trong đó:

• Range: Là vùng được chọn có chứa các ô điều kiện.
• Criteria: Là điều kiện để thực hiện hàm này.
• Sum_range: Vùng cần tính tổng.

Ví dụ 1: Để tính giá trị sản phẩm > 3.000.000, bạn nhập hàm như sau: =SUMIF(D2:D8,">3000000",E2:E8)

Trong đó:

• D2:D8: Là vùng dữ liệu dữ liệu cho điều kiện bài toán.
• ">3000000": Là điều kiện yêu cầu của bài toán cần tìm.
• E2:E8: Là vùng dữ liệu lấy kết quả.

Sau đó ấn Enter để nhận kết quả.

Nhập công thức hàm SUMIF có điều kiện trong Excel như hình

Kết quả tính tổng khi sử dụng hàm SUMIF trong Excel

Tương tự, bạn có thể thực hiện tính tổng trong Excel với các điều kiện khác nhau như:

• Bạn nhập hàm: =SUMIF(B2:B8,"Trái cây",E2:E8) để tính tổng giá tiền của nhóm trái cây trong bảng.
• Nhập hàm: =SUMIF(B2:B8,"",E2:E8) để tính tổng giá tiền của quả các thực phẩm không nằm trong nhóm nào

Hàm tính tổng giá tiền của quả các thực phẩm không nằm trong nhóm nào

Hàm SUMIFS cho phép người dùng tính tổng các giá trị trong một phạm vi theo điều kiện trước đó. SUMIFS hoạt động tương tự như hàm SUMIF, nhưng cho phép người dùng tính tổng nhiều điều kiện trong Excel.

Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Trong đó:

• Sum_range (bắt buộc): Là vùng cần tính tổng trong bảng dữ liệu, trong đó giá trị trống và giá trị text được bỏ qua.
• Criteria_range1 (bắt buộc): Là phạm vi cần được kiểm tra bằng điều kiện criteria1.
• Criteria1( bắt buộc): Là tiêu chí xác định ô nào trong Criteria_range1 sẽ được tính tổng
• Criteria_range2,criteria2,…: Các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/tiêu chí.

Ví dụ: Giả sử bạn có một bảng tổng hợp các lô hàng trái cây được cung cấp bởi các nhà cung ứng khác nhau. Bạn cần tính tổng sản lượng táo được cung cấp bởi Phát.

Bảng dữ liệu tổng hợp các lô hàng trái cây

Như vậy, công thức hàm SUMIF nhiều điều kiện trong Excel sẽ tính tổng các giá trị tại cột C dựa theo hai điều kiện tương ứng: giá trị tại cột A là "Táo" và giá trị tại cột B là "Phát". Cách xác định các yếu tố trong hàm SUMIFS như sau:

• sum_range: Phạm vi cần tính tổng là cột số lượng hàng hóa (Cột C) C2:C9.
• criteria_range1: Phạm vi đáp ứng điều kiện đầu tiên là tên mặt hàng (Cột A) A2:A9
• criteria1: Điều kiện áp dụng cho phạm vi A2:A9 là ô tính có nội dung “Táo”
• criteria_range2: Phạm vi đáp ứng điều kiện thứ hai là tên nhà cung ứng (Cột B) B2:B9
• criteria2: Điều kiện áp dụng cho phạm vi B2:B9 là ô tính có nội dung “Phát”

Khi đó bạn nhập hàm: = SUMIFS(C2:C9,A2:A9,“Táo”, B2:B9, “Phát”) và nhấn Enter để nhận kết quả.

Nhập hàm như hình 

SUMPRODUCT là một hàm hữu ích trong Excel với khả năng xử lý mảng thông minh. Về cơ bản, SUMPRODUCT là hàm kết hợp giữa hai hàm tính tổng SUM và hàm tính tích. Hàm SUMPRODUCT sẽ trả về tổng của tích các dải ô hoặc mảng tương ứng.

Cú pháp: =SUMPRODUCT(array1, [array2], [array3], …)

Trong đó:

• array1 (bắt buộc): Đối số mảng đầu tiên mà bạn muốn nhân các thành phần của nó rồi cộng tổng.
• [array2], [array3],… (tùy chọn): Các đối số mảng từ 2 đến 255 mà bạn muốn nhân các ô của nó với array1 rồi cộng tổng.

Ví dụ 1: Để tính tổng doanh thu của sản phẩm “trà” tại khu vực phía Bắc, bạn nhập hàm như sau: =SUMPRODUCT((B3:B10="Trà"(C3:C10="Bắc")D3:D10)

Tính tổng nhiều điều kiện trong Excel bằng hàm SUMPRODUCT

Ngoài ra, bạn cũng có thể kết hợp hàm SUMPRODUCT với OR để tính tổng doanh thu của trà hoặc cà phê tại khu vực phía Bắc bằng cách nhập: 

=SUMPRODUCT(OR((B3:B10="Trà";(B3:B10="Cà phê"))(C3:C10="Bắc")D3:D10)

Ví dụ 2: Để tính tổng các giá trị trong một dãy C2:C7 nếu chúng lớn hơn 6, bạn sử dụng công thức sau: =SUMPRODUCT((C2:C7>6)(C2:C7)).

Nhập hàm tính tổng có điều kiện như hình

Lưu ý: 

• Tất cả các mảng trong công thức hàm SUMPRODUCT phải có cùng số lượng hàng và cột, nếu không sẽ bị lỗi #VALUE! 
• Nếu bất kỳ đối số mảng nào chứa các giá trị không phải là số, Excel sẽ coi giá trị đó là số 0.
• Nếu một mảng là một bài kiểm tra logic, nó sẽ dẫn đến các giá trị TRUE và FALSE. Trong hầu hết các trường hợp, bạn cần chuyển đổi chúng thành 1 và 0 bằng cách sử dụng toán tử đơn nguyên kép (-). 
• Tổng hợp không hỗ trợ các ký tự đại diện.

4. Hàm SUBTOTAL

Hàm SUBTOTAL là hàm trả về tổng phụ của một chuỗi giá trị. Tổng phụ tức là tổng của một số giá trị thỏa mãn một điều kiện chung nào đó.

Cú pháp: =SUBTOTAL(function_num,ref1,[ref2],…)

Trong đó:

• Function_num: là con số xác định hàm thực hiện phép tính.
• Ref1, Ref2, …: là 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ.

Ví dụ: Giả sử bạn có bảng tổng KPI như dưới đây và cần lọc ra tổng KPI của đội A. Bạn thực hiện như sau:

Bước 1: Bôi đen bảng chứa dữ liệu cần tính > Vào Home > Sort and Filter > Filter.

Chọn Filter 

Bước 2: Bấm chọn dấu mũi tên hình tam giác ngược ở tiêu đề cột "Đội", tích chọn "A" và nhấn OK.

Lọc đội A

Bước 3: Bảng dữ liệu của đội A được Excel lọc ra. Lúc này, tại ô cần lấy kết quả bạn nhập:

=SUBTOTAL(9,D2:D11)

Trong đó: 

• function_num = 9 là giá trị đối số tương ứng với hàm cần sử dụng. Ở đây chúng ta cần tính tổng nên sẽ chọn hàm SUM - tương ứng với số 9.
• ref1 = D2:D11 là phạm vi cần tính tổng.

Sau khi nhập hàm và nhấn Enter, bạn được kết quả như sau:

Nhập công thức tính tổng trong Excel có điều kiện bằng hàm SUBTOTAL

Lưu ý: Bạn sử dụng giá trị đối số là 9 để tính tổng các giá trị đã bị ẩn hoặc 109 để bỏ qua các giá trị đã bị ẩn và tính tổng các hàng đã lọc.

Trên đây là 4 hàm phổ biến, giúp bạn tính tổng trong Excel có điều kiện với mọi trường hợp và biến số khác nhau. Chúc bạn thực hiện thành công!