Microsoft Excel (Bài 80): Cách dùng SUMIF trong Excel với nhiều điều kiện (hàm logic OR)

Microsoft Excel có một chức năng đặc biệt để tính tổng các ô có nhiều điều kiện – hàm SUMIFS. Hàm này được thiết kế để tính tổng các giá trị có nhiều điều kiện đồng thời xảy ra (AND) – một ô chỉ được thêm vào khi tất cả các tiêu chí được chỉ định là TRUE cho ô đó. Tuy nhiên, trong một số trường hợp, bạn có thể tỉnh tổng với nhiều tiêu chí không đồng thời xảy ra, nghĩa là thêm một ô khi bất kỳ điều kiện riêng lẻ nào là đúng. Và đây là lúc sử dụng hàm SUMIF.

SUMIF + SUMIF ĐỂ TÍNH TỔNG CÁC Ô BẰNG CÁI NÀY HOẶC CÁI KIA

Nếu bạn đang muốn tính tổng các số trong một cột và sau đó tính tổng của các cột đó thì giải pháp rõ ràng nhất là xử lý từng điều kiện riêng lẻ sau đó cộng các kết quả lại với nhau.

SUMIF(range, criteria1, sum_range) + SUMIF(range, criteria2, sum_range)

Trong bảng bên dưới, giả sử bạn muốn cộng doanh số cho hai sản phẩm khác nhau như Apple và Lemon. Đối với điều kiện này, bạn có thể nhập các mục quan tâm trực tiếp như “ apple” và “ lemon” vào tiêu chí của 2 hàm SUMIF khác nhau:

=SUMIF(A2:A10, “apple”, B2:B10) + SUMIF(A2:A10, “lemon”, B2:B10)

Hoặc bạn có thể nhập tiêu chí vào các ô riêng biệt và tham chiếu các ô đó:

=SUMIF(A2:A10, E1, B2:B10) + SUMIF(A2:A10, E2, B2:B10)

Trong đó, A2:A10 là danh sách các mục (phạm vi), B2:B10 là các số để tính tổng (sum_range), E1 và E2 là các mục tiêu chí:

Cách hoạt động của công thức này:

Hàm SUMIF đầu tiên cộng doanh số của Apple, hàm SUMIF thứ hai tỉnh tổng doanh số lemon. Công thức này sẽ cộng các tổng phụ với nhau và xuất ra tổng chính.

SUMIF VỚI HẰNG SỐ MẢNG – CÔNG THỨC THU GỌN VỚI NHIỀU TIÊU CHÍ

Phương pháp SUMIF + SUMIF hoạt động tốt khi có 2 điều kiện. Trong trường hợp nếu bạn muốn tỉnh tổng với 3 điều kiện trở lên, công thức trên sẽ quá lớn và khó đọc. Thay vào đó sử dụng SUMIF với hằng số mảng, công thức nhỏ gọn hơn và vẫn đạt kết quả tương tự:

SUM(SUMIF(range, {criteria1, criteria2, criteria3, …}, sum_range))

Hãy nhớ rằng công thức này hoạt động dựa trên các tiêu chí không đồng thời xảy ra (OR) – một ô được tính tổng khi bất kỳ điều kiện đơn lẻ nào được đáp ứng.

Trong trường hợp này, để tính tổng doanh số cho 3 mặt hàng khác nhau, công thức là:

=SUM(SUMIF(A2:A10, {“Apples”,”Lemons”,”Oranges”}, B2:B10))

Trong bức hình trên, các điều kiện được mã hóa cứng trong một mảng, có nghĩa bạn phải cập nhật công thức với mọi thay đổi trong điều kiện. Để tránh điều này, bạn có thể nhập điều kiện vào các ô được xác định trước và tạo cho công thức dưới dạng tham chiếu phạm vi (E1:E3 trong ví dụ này).

=SUM(SUMIF(A2:A10, E1:E3, B2:B10))

Trong Excel 365 hỗ trợ mảng động (dynamic array), nó hoạt động như một công thức thông thường và được hoàn thành bằng phím ENTER. Trong các phiên bản của Excel 2019, Excel 2016, Excel 2013 trở về trước, công thức mảng động được nhập dưới dạng phím tắt CTRL + SHIFT+ENTER.

Hoạt động của công thức này

Một hằng số mảng mà được được gắn vào các điều kiện của SUMIF buộc nó phải trả về nhiều kết quả dưới dạng một mảng. Trong trường hợp này, đó là 3 lượng khác nhau: apple, lemon và orange.

{425;425;565}

Để có được tổng số, chúng ta sử dụng hàm Sum và hỗ trợ công thức SUMIF.

SUMPRODUCT VÀ SUMIF ĐỂ TÍNH TỔNG CÁC Ô CÓ NHIỀU ĐIỀU KIỆN OR

Bạn không muốn sử dụng mảng và đang tìm kiếm một công thức bình thường giúp bạn tính tổng với nhiều điều kiện trong các ô khác nhau? Thay vì Sum thì bạn có thể sử dụng hàm SUMPRODUCT xử lý mảng nguyên bản:

SUMPRODUCT(SUMIF(range, crireria_range, sum_range))

Giả sử các điều kiện nằm trong các ô E1, E2 và E3 thì công thức có dạng như sau:

=SUMPRODUCT(SUMIF(A2:A10, E1:E3, B2:B10))

Công thức này hoạt động như thế nào:

Giống với ví dụ trước, hàm SUMIF trả về một mảng số, đại diện cho tổng từng điều kiện riêng lẻ. SUMPRODUCT cộng các số này lại với nhau và xuất ra tổng cuối. Không giống với hàm SUM, hàm SUMPRODUCT được thiết kế để xử lý mảng, vì vậy nó hoạt động như một công thức thông thường mà bạn không cần sử dụng tổ hợp phím “ CTRL + Shift + Enter”.

SUMIF SỬ DỤNG NHIỀU TIÊU CHÍ VỚI CÁC KÝ TỰ ĐẠI DIỆN

Vì hàm SUMIF trong Excel hỗ trợ các ký tự đại diện nên bạn có thể đưa chúng vào nhiều tiêu chí khi bạn cần.

Ví dụ để tính tổng doanh số bán hàng cho tất cả các loại apples và banana, công thức là:

=SUM(SUMIF(A2:A10, {“*Apples”,”*Bananas”}, B2:B10))

Nếu điều kiện của bạn nhập vào các ô riêng lẻ, bạn có thể nhập ký tự đại diện trực tiếp vào các ô đó và cung cấp tham chiếu phạm vi làm tiêu chí cho công thức SUMPRODUCT SUMIF:

Trong ví dụ này, chúng ta đặt ký tự đại diện (*) trước tên mặt hàng để khớp với bất kỳ chuỗi ký tự nào đứng trước như Green Apples và chuối Goldfinger, Để nhận tổng số bất kỳ vị trí nào trong ô hãy đặt dấu hoa thị ở cả 2 bên, ví dụ “*apple*”.