Công thức chung
=SUMPRODUCT(--((ISNUMBER(FIND("abc",rng)) + ISNUMBER(FIND("def",rng)))>0))
Tóm lược
Để đếm các ô chứa x hoặc y, bạn có thể sử dụng công thức dựa trên hàm SUMPRODUCT. Trong ví dụ được hiển thị, công thức trong ô F5 là:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11))+ISNUMBER(FIND("def",B5:B11)))>0))
Đây là giải pháp công thức ô đơn, được giải thích bên dưới. Cũng có thể sử dụng một công thức đơn giản hơn dựa trên cột trợ giúp, cũng được giải thích bên dưới.
Giải trình
Khi bạn đếm các ô với "logic HOẶC", bạn cần phải cẩn thận không đếm hai lần. Ví dụ: nếu bạn đang đếm các ô có chứa "abc" hoặc "def", bạn không thể chỉ cộng hai hàm COUNTIF lại với nhau, vì bạn có thể đếm gấp đôi các ô chứa cả "abc" và "def".
Giải pháp tế bào đơn
Đối với một công thức, bạn có thể sử dụng SUMPRODUCT với ISNUMBER + FIND. Công thức trong F5 là:
=SUMPRODUCT(--((ISNUMBER(FIND("abc",B5:B11)) + ISNUMBER(FIND("def",B5:B11)))>0))
Công thức này dựa trên công thức được giải thích ở đây để định vị văn bản bên trong ô:
ISNUMBER(FIND("abc",B5:B11)
Khi được cung cấp một dải ô, đoạn mã này sẽ trả về một mảng các giá trị TRUE / FALSE, một giá trị cho mỗi ô trong phạm vi. Vì chúng ta đang sử dụng điều này hai lần (một lần cho "abc" và một lần cho "def"), chúng ta sẽ nhận được hai mảng.
Tiếp theo, chúng ta thêm các mảng này lại với nhau (với +), điều này tạo ra một mảng số mới. Mỗi số trong mảng này là kết quả của việc cộng các giá trị TRUE và FALSE trong hai mảng ban đầu với nhau. Trong ví dụ được hiển thị, mảng kết quả trông giống như sau:
(2;0;2;0;1;0;2)
Chúng ta cần cộng những con số này lên, nhưng chúng ta không muốn đếm gấp đôi. Vì vậy, chúng ta cần đảm bảo rằng mọi giá trị lớn hơn 0 đều chỉ được tính một lần. Để làm điều đó, chúng tôi buộc tất cả các giá trị là TRUE hoặc FALSE với "> 0", sau đó buộc thành 1/0 với âm kép (-).
Cuối cùng, hàm SUMPRODUCT trả về tổng của tất cả các giá trị trong mảng.
Giải pháp cột trợ giúp
Với cột trợ giúp để kiểm tra từng ô riêng lẻ, vấn đề sẽ ít phức tạp hơn. Chúng ta có thể sử dụng COUNTIF với hai giá trị (được cung cấp dưới dạng "hằng số mảng"). Công thức ở C5 là:
=--(SUM(COUNTIF(B5,("*abc*","*def*")))>0)
COUNTIF sẽ trả về một mảng chứa hai mục: số đếm cho "abc" và số đếm cho "def". Để tránh đếm hai lần, chúng tôi thêm các mục lên và sau đó buộc kết quả thành TRUE / FALSE với "> 0". Cuối cùng, chúng tôi chuyển đổi các giá trị TRUE / FALSE thành 1 và 0 bằng dấu kép (-).
Kết quả cuối cùng là 1 hoặc 0 cho mỗi ô. Để nhận tổng cho tất cả các ô trong phạm vi, chỉ cần tính tổng cột trợ giúp.
