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 hằng số mảng như sau:
("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 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 "màu đỏ" hoặc "màu 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ột mảng cột duy nhất cho một tiêu chí và một mảng hàng cho tiêu chí kia. 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 trong hằng số mảng thứ hai, biểu thị 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ư sau:
=(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.
