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

Trong bài viết này, Gitiho sẽ hướng dẫn các bạn cách đếm các điều kiện loại trừ trong Excel với hàm SUMPRODUCT. Hãy cùng tìm hiểu kiến thức cực thú vị này nhé!

Hàm SUMPRODUCT trong Excel có công dụng là trả về tổng của tổng của các dải ô hoặc mảng tương ứng. Bạn có thểm ứng dụng hàm này để đếm các điều kiện loại trừ trong bảng tính Excel theo những cách được hướng dẫn dưới đây:

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

Chúng mình cùng xem bảng ví dụ dưới đây nhé:

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:

Xem thêm: Hướng dẫn cách dùng hàm COUNTIFS và SUMPRODUCT trong việc đếm theo nhiều điều kiện trên Excel

Đế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))

Xem thêm: Hướng dẫn đếm ô chứa giá trị nhất định với hàm SUMPRODUCT

Đế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 là:

=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.