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 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 phạm vi đáp ứng 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 hằng số mảng bên trong hàm INDIRECT như sau:

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. 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 hàm dễ thay đổi và có thể ảnh hưởng đến hiệu suất của 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, do đó các phạm vi sẽ tự động cập nhật với các thay đổi trang tính.