Công thức Excel: COUNTIF với phạm vi không liền kề
Công thức Excel: COUNTIF với phạm vi không liền kề

Công thức chung
=SUM(COUNTIF(INDIRECT({"rng1","rng2","rng3"}),criteria))
Tóm lược

Để sử dụng đếm một phạm vi không liền kề với tiêu chí, bạn có thể sử dụng hàm COUNTIF cùng với INDIRECT và SUM. Trong ví dụ được hiển thị, ô I5 chứa công thức này:

=SUM(COUNTIF(INDIRECT({"B5:B8","D7:D10","F6:F11"}),">50"))

Giải trình

COUNTIF đếm số ô trong một dải ô đáp ứng các tiêu chí nhất định. Nếu bạn cố gắng sử dụng COUNTIF với nhiều phạm vi được phân tách bằng dấu phẩy, bạn sẽ gặp lỗi. Một giải pháp là viết ra các phạm vi dưới dạng văn bản trong một mảng hằng số bên trong hàm INDIRECT như thế này:

INDIRECT({"B5:B8","D7:D10","F6:F11"})

INDIRECT sẽ đánh giá các giá trị văn bản và chuyển nhiều phạm vi thành COUNTIF. Bởi vì COUNTIF nhận nhiều hơn một dải ô, nó sẽ trả về nhiều hơn một kết quả trong một mảng. Chúng tôi sử dụng hàm SUM để “bắt” và xử lý mảng:

=SUM({4,2,3})

Sau đó, hàm SUM trả về tổng của tất cả các giá trị, 9. Mặc dù đây là một công thức mảng, nhưng nó không yêu cầu CSE, vì chúng ta đang sử dụng một hằng số mảng.

Lưu ý: INDIRECT là một chức năng dễ bay hơi và có thể ảnh hưởng đến hiệu suất sổ làm việc.

Nhiều COUNTIF

Một cách khác để giải quyết vấn đề này là sử dụng nhiều hơn một COUNTIF:

=COUNTIF(B5:B8,">50")+COUNTIF(D7:D10,">50")+COUNTIF(F6:F11,">50")

Với một số phạm vi hạn chế, cách tiếp cận này có thể dễ thực hiện hơn. Nó tránh các tác động hiệu suất có thể có của INDIRECT và cho phép một cú pháp công thức bình thường cho các phạm vi, vì vậy các phạm vi sẽ tự động cập nhật với các thay đổi trang tính.

Phạm vi ô đơn

Với các phạm vi ô đơn, bạn có thể viết công thức không có COUNTIF như sau:

=(A1>50)+(C1>50)+(E1>50)

Mỗi biểu thức trả về TRUE hoặc FALSE, khi bị ép buộc về 1 và 0 trong phép toán. Đây là một ví dụ về việc sử dụng logic boolean trong một công thức.

https://exceljet.net/formula/countif-with-non-contiguous-range