Công thức Excel: Đếm số lần xuất hiện trong toàn bộ sổ làm việc -

Mục lục

Công thức chung

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!"&rng),criteria))

Tóm lược

Để đếm kết quả phù hợp trong toàn bộ sổ làm việc, bạn có thể sử dụng công thức dựa trên các hàm COUNTIF và SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong D5 là:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheets&"'!A1:Z10000"),B5))

trong đó "trang tính" là phạm vi được đặt tên B8: B10.

Bối cảnh

Sổ làm việc này có bốn trang tính. Ba trong số các bảng tính, "Sheet1", "Sheet2" và "Sheet3" chứa 1000 tên đầu tiên ngẫu nhiên trong một bảng trông giống như sau:

Lưu ý: phạm vi chúng tôi đang sử dụng trong công thức, A1: Z10000, là tùy ý và phải được điều chỉnh cho phù hợp với dữ liệu của bạn.

Giải trình

Làm việc từ trong ra ngoài, trước tiên chúng ta có biểu thức này bên trong hàm INDIRECT:

"'"&sheets&"'!A1:Z10000"

Bởi vì "trang tính" là một phạm vi được đặt tên có chứa "Trang tính1", "Trang tính2" và "Trang tính3", chúng tôi nhận được một mảng như thế này khi biểu thức được đánh giá:

("'Sheet1'!A1:Z10000";"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000")

Bên trong mảng, chúng ta có ba giá trị và mỗi giá trị là một tên trang tính được nối bằng cách ghép với phạm vi A1: Z10000. Lưu ý rằng đây là tất cả các giá trị văn bản.

Tiếp theo, hàm INDIRECT được sử dụng để chuyển đổi mỗi giá trị văn bản thành một tham chiếu thích hợp, được cung cấp cho hàm COUNTIF dưới dạng đối số phạm vi, cùng với giá trị trong D5 cho tiêu chí.

Vì chúng tôi đã cung cấp cho COUNTIF ba phạm vi riêng biệt, chúng tôi nhận được ba kết quả trong mảng như sau: