Công thức Excel: SUMIFS với nhiều tiêu chí và logic HOẶC
Công thức Excel: SUMIFS với nhiều tiêu chí và logic HOẶC

Công thức chung
=SUM(SUMIFS(sum_range,criteria_range,{"red","blue"}))
Tóm lược

Để tính tổng dựa trên nhiều tiêu chí bằng cách sử dụng logic HOẶC, bạn có thể sử dụng hàm SUMIFS với một hằng số mảng. Trong ví dụ được hiển thị, công thức trong H6 là:

=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"}))

Giải trình

Theo mặc định, hàm SUMIFS chỉ cho phép logic AND – khi bạn cung cấp nhiều điều kiện, tất cả các điều kiện phải khớp để được đưa vào kết quả.

Một giải pháp là cung cấp nhiều tiêu chí trong một mảng hằng số như thế này:

{"complete","pending"}

Điều này sẽ khiến SUMIFS trả về hai kết quả: một số cho “hoàn thành” và một số cho “đang chờ xử lý”, trong một kết quả mảng như thế này:

{100,35}

Để có tổng số cuối cùng, chúng tôi đặt SUMIFS bên trong SUM. Hàm SUM tính tổng tất cả các mục trong mảng và trả về kết quả.

Với các ký tự đại diện

Bạn có thể sử dụng các ký tự đại diện trong tiêu chí nếu cần. Ví dụ: để tính tổng các mục có chứa “đỏ” hoặc “xanh lam” ở bất kỳ vị trí nào trong dải tiêu chí, bạn có thể sử dụng:

=SUM(SUMIFS(sum_range,criteria_range,{"*red*","*blue*"}))

Thêm tiêu chí HOẶC khác

Bạn có thể thêm một tiêu chí bổ sung vào công thức này, nhưng bạn sẽ cần sử dụng mảng cột đơn cho một tiêu chí và một hàng mảng Cho người khác. Vì vậy, ví dụ: để tổng hợp các đơn đặt hàng “Hoàn thành” hoặc “Đang chờ xử lý”, cho “Andy Garcia” hoặc “Bob Jones”, bạn có thể sử dụng:

=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"},C4:C11,{"Bob Jones";"Andy Garcia"}))

Lưu ý dấu chấm phẩy ở dấu hai chấm mảng hằng số, đại diện cho một mảng dọc. Điều này hoạt động vì Excel “ghép nối” các phần tử trong hai hằng số mảng và trả về một mảng kết quả hai chiều. Với nhiều tiêu chí hơn, bạn sẽ muốn chuyển sang công thức dựa trên SUMPRODUCT.

Tham chiếu ô cho tiêu chí

Bạn không thể sử dụng tham chiếu ô bên trong một hằng số mảng. Để sử dụng tham chiếu ô cho tiêu chí, bạn có thể sử dụng công thức mảng như thế này:

={SUM(SUMIFS(range1,range2,range3))}

Trong đó range1 là phạm vi tổng, range2 là phạm vi tiêu chí và range3 chứa tiêu chí trên trang tính. Với hai tiêu chí HOẶC, bạn sẽ cần sử dụng các mảng ngang và dọc.

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

https://exceljet.net/formula/sumifs-with-multiple-criteria-and-or-logic