Hướng dẫn đếm các điều kiện loại trừ với SUMPRODUCT

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, ISNACHỈ 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ừ:

>>> Xem thêm:  Cách sửa file Excel bị nặng chậm, và gỡ bỏ mật khẩu bảo vệ

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