Trong bài viết dưới đây Học Excel Online sẽ hướng dẫn bạn cách đếm các điều kiện loại trừ bằng SUMPRODUCT.
Điều kiện loại trừ tổng số MATCH và SUMPRODUCT kết hợp
Chúng ta sẽ đến với bảng ví dụ sau:
Lời yêu cầu: Đếm số bút bi là loại A và không phải loại A.
Đặt phạm vi điều kiện
Với yêu cầu này, chúng ta sẽ thiết lập vùng điều kiện như hình:
Đếm các điều kiện được thỏa mãn
Trước khi đi vào đếm các điều kiện loại trừ, chúng ta sẽ đếm các điều kiện được thỏa mãn. Để tiếp tục, chúng tôi chỉ cần sử dụng hàm GIỚI THIỆU như sau:
=GIỚI THIỆU((Khu vực 1= điều kiện 1)*(vùng 2 =điều kiện 2))
Đặc biệt: = SUMPRODUCT ((A2: A15 = D2) * (B2: B15 = E2))
Đếm các điều kiện loại trừ
Với yêu cầu trên, ta có thể giải theo phương pháp: Đếm các loại B, C, D rồi tính tổng; hoặc đếm kiểu tổng rồi trừ kiểu A. Ta có thể thực hiện phép tính trừ bằng cách kết hợp GIỚI THIỆU, ISNA và CHỈ TRẬN ĐẤU.
Công thức
Công thức chung trong trường hợp này:
=GIỚI THIỆU((Khu vực 1= điều kiện 1)*ISNA(CHỈ TRẬN ĐẤU(khu 2điều kiện 2,)))
Có ý nghĩa
Đầu tiên, với hàm MATCH, chúng ta sẽ tìm vị trí của mỗi giá trị trong vùng 2 thỏa mãn điều kiện 2:
Kết hợp với hàm ISNA để trả về giá trị TRUE / FALSE:
Cuối cùng kết hợp với SUMPRODUCT để đếm theo điều kiện loại trừ:
Trong trường hợp này, bạn hoàn toàn có thể sử dụng công thức chỉ với GIỚI THIỆU để thay thế những thứ sau:
= SUMPRODUCT ((A2: A15 = D2) * (B2: B15 <> E2))
Tuy nhiên, đối với nhiều loại trừ (chẳng hạn như số lượng bút chì không phải A hay B), hàm MATCH sẽ là một giải pháp hữu ích.
Xem các bài viết tương tự về CHỈ SUMPRODUCT, ISNA và MATCH Sau đây:
Hướng dẫn sử dụng hàm ISNA thông qua các ví dụ
Tìm hiểu xem các ô có trống không với SUMPRODUCT
Kết hợp INDEX và MATCH trong Excel
Nguồn : Blog hocexcel