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.

Kết luận

Qua bài viết này, chắc hẳn bạn đã biết được cách để đếm các điều kiện loại trừ trong Excel. Hãy ứng dụng những kiến thức được chia sẻ trong bài viết này vào công việc của bạn nhé. Nếu bạn vẫn còn điều thắc mắc chưa hiểu thì hãy để lại bình luận để chúng mình giải đáp nha.

Những kiến thức như thế này bạn có thể học được trong khóa Tuyệt đỉnh Excel của Gitiho với hướng dẫn chi tiết hơn của các chuyên gia:

Tuyệt đỉnh Excel: Trở thành bậc thầy Excel sau 16 giờ

Khóa học bao gồm các bài giảng từ cơ bản đến nâng cao về Excel. Dù bạn đang ở trình độ nào thì cũng có thể tham gia khóa học. Bạn sẽ được rất nhiều các kỹ năng để làm việc nhanh và hiệu quả trên Excel. Ngoài phần lý thuyết thì còn có các bài tập ứng dụng thực tế và những tài liệu hay được tặng kèm trong khóa học. Tham gia khóa học để trang bị những kiến thức hay giúp ích cho công việc cũng như sự phát triển năng lực của bản thân các bạn nhé.

Chúc các bạn luôn học tập và làm việc hiệu quả!