Công thức chung
=SUMPRODUCT(--(range1=criteria),range2)
Tóm lược
Để tổng hoặc tổng các cột dựa trên tiêu chí trong các cột liền kề, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong J5 là:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Giải trình
Về cơ bản, công thức này sử dụng SUMPRODUCT để nhân rồi tính tổng các tích của hai mảng: array1 và array2 . Mảng đầu tiên, array1 được thiết lập để hoạt động như một "bộ lọc" để chỉ cho phép các giá trị đáp ứng tiêu chí.
Array1 sử dụng một phạm vi bắt đầu trên cột đầu tiên chứa các giá trị phải vượt qua tiêu chí. Những "giá trị tiêu chí" này nằm trong một cột ở bên trái và ngay cạnh "giá trị dữ liệu".
Tiêu chí được áp dụng như một bài kiểm tra đơn giản tạo ra một mảng các giá trị TRUE và FALSE:
--($B5:$H5=J$4)
Bit này của công thức "kiểm tra" từng giá trị trong mảng đầu tiên bằng cách sử dụng tiêu chí được cung cấp, sau đó sử dụng dấu kép (-) để ép các giá trị TRUE và FALSE kết quả thành 1 và 0. Kết quả trông như thế này:
(1,0,0,0,1,0,1)
Lưu ý rằng 1s tương ứng với cột 1,5 và 7, đáp ứng tiêu chí của "A".
Đối với mảng 2 bên trong SUMPRODUCT, chúng tôi sử dụng một dải ô được "dịch chuyển" bởi một cột sang bên phải. Phạm vi này bắt đầu với cột đầu tiên chứa các giá trị để tính tổng và kết thúc bằng cột cuối cùng chứa các giá trị để tính tổng.
Vì vậy, trong công thức ví dụ trong J5, sau khi các mảng đã được điền, chúng ta có:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Vì SUMPRODUCT được lập trình đặc biệt để bỏ qua các lỗi do nhân các giá trị văn bản, nên mảng cuối cùng trông giống như sau:
(1,0,0,0,1,0,1)
Các giá trị duy nhất mà phép nhân "tồn tại" là những giá trị tương ứng với 1s bên trong array1 . Bạn có thể nghĩ về logic trong array1 "lọc" các giá trị trong array2 .
