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ừ với SUMPRODUCT.

Kết hợp MATCH và SUMPRODUCT đếm các điều kiện loại trừ

Ta sẽ đến với bảng ví dụ sau đây:

Yêu cầu: Đếm số lượng bút bi là loại A, và không phải loại A.

Thiết lập vùng điều kiện

Với yêu cầu này, ta sẽ thiết lập vùng điều kiện như hình:

Đếm điều kiện thỏa mãn

Trước khi đi vào đếm điều kiện loại trừ, ta sẽ cùng đếm điều kiện thỏa mãn. Để tiến hành, ta sử dụng đơn giản hàm SUMPRODUCT như sau:

=SUMPRODUCT((vùng 1=điều kiện 1)*(vùng 2=điều kiện 2))

Cụ thể: =SUMPRODUCT((A2:A15=D2)*(B2:B15=E2))

Đếm đ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 tổng loại rồi trừ đi loại A. Ta có thể thực hiện cách đếm loại trừ bằng cách kết hợp SUMPRODUCT, ISNA và MATCH.

Công thức

Công thức tổng quát trong trường hợp này:

=SUMPRODUCT((vùng 1=điều kiện 1)*ISNA(MATCH(vùng 2, điều kiện 2,0)))

Ý nghĩa

Đầu tiên, với hàm MATCH ta sẽ tìm ra vị trí của từng giá trị trong vùng 2 thỏa mãn điều kiện 2:

Kết hợp với hàm ISNA để đưa giá trị về 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 SUMPRODUCT để thay thế như sau:

=SUMPRODUCT((A2:A15=D2)*(B2:B15<>E2))

Tuy nhiên, đối với nhiều điều kiện loại trừ (chẳng hạn số bút chì không phải A hoặc B), hàm MATCH sẽ là một giải pháp hữu ích.

Tham khảo các bài viết tương tự về SUMPRODUCT, ISNA và MATCH sau đây:

Hướng dẫn sử dụng hàm ISNA qua các ví dụ

Nhận biết các ô đều trống với SUMPRODUCT

Kết hợp INDEX và MATCH trong Excel