Hàm Excel COUNTIF Và COUNTIFS Với Các điều Kiện OR

Như mọi người đều biết, hàm COUNTIF của Excel được thiết kế để đếm các ô dựa trên chỉ một tiêu chí trong khi COUNTIFS đánh giá nhiều tiêu chí với logic AND. Nhưng điều gì sẽ xảy ra nếu nhiệm vụ của bạn yêu cầu logic OR – khi một số điều kiện được cung cấp, bất kỳ điều kiện nào có thể khớp để được đưa vào số đếm?

Có một vài giải pháp khả thi cho nhiệm vụ này và hướng dẫn này sẽ đề cập đến tất cả chúng một cách chi tiết. Các ví dụ ngụ ý rằng bạn có kiến ​​thức vững chắc về cú pháp và cách sử dụng chung của cả hai hàm. Nếu không, bạn có thể muốn bắt đầu với việc sửa đổi những điều cơ bản:

Hàm COUNTIF của Excel – đếm các ô với một tiêu chí.

Hàm COUNTIFS của Excel – đếm các ô có nhiều tiêu chí AND.

Bây giờ mọi người đang ở trên cùng một trang, hãy đi sâu vào:

Đếm các ô có điều kiện OR trong Excel

Phần này bao gồm kịch bản đơn giản nhất – đếm các ô đáp ứng bất kỳ (ít nhất một) điều kiện được chỉ định.

Công thức 1. Hàm COUNTIF + COUNTIF

Cách dễ nhất để đếm các ô có giá trị này hoặc giá trị khác (Countif một hoặc là b) là viết một công thức COUNTIF thông thường để đếm từng mục riêng lẻ, sau đó thêm kết quả:

COUNTIF (phạm vi, tiêu chí1) + COUNTIF (phạm vi, tiêu chí2)

Ví dụ: hãy tìm hiểu có bao nhiêu ô trong cột A chứa “táo” hoặc “chuối”:

=COUNTIF(A:A, "apples") + COUNTIF(A:A, "bananas")

Trong các bảng tính thực tế, cách tốt nhất là vận hành trên các phạm vi thay vì toàn bộ các cột để công thức hoạt động nhanh hơn. Để khắc phục sự cố cập nhật công thức của bạn mỗi khi điều kiện thay đổi, hãy nhập các mục quan tâm vào các ô được xác định trước, nói F1 và G1 và tham chiếu các ô đó. Ví dụ:

=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)

Kỹ thuật này hoạt động tốt đối với một vài tiêu chí, nhưng việc thêm ba hoặc nhiều hàm COUNTIF lại với nhau sẽ khiến công thức trở nên quá cồng kềnh. Trong trường hợp này, bạn nên gắn bó với một trong những lựa chọn thay thế sau đây.

Công thức 2. Hàm COUNTIF với hằng số mảng

Đây là phiên bản nhỏ gọn hơn của SUMIF với công thức điều kiện OR trong Excel:

SUM (COUNTIF (phạm vi, {tiêu chí1, tiêu chí2, tiêu chí3, Lọ}))

Công thức được xây dựng theo cách này:

Trước tiên, bạn đóng gói tất cả các điều kiện trong một hằng số mảng – các mục riêng lẻ được phân tách bằng dấu phẩy và mảng được đặt trong dấu ngoặc nhọn như {“táo”, “chuối ‘,” chanh “}.

Sau đó, bạn bao gồm hằng số mảng trong tiêu chí đối số của một công thức COUNTIF bình thường: COUNTIF (A2: A10, {“táo”, “chuối”, “chanh”})

Cuối cùng, warp công thức COUNTIF trong hàm SUM. Điều này là cần thiết bởi vì COUNTIF sẽ trả lại 3 số đếm riêng cho “táo”, “chuối” và “chanh” và bạn cần cộng các số đó lại với nhau.

Công thức hoàn chỉnh của chúng tôi diễn ra như sau:

=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))

Nếu bạn muốn cung cấp tiêu chí của bạn như tham chiếu phạm vi, bạn sẽ cần nhập công thức với Ctrl + Shift + Enter để làm cho nó một công thức mảng. Ví dụ:

=SUM(COUNTIF(A2:A10,F1:H1))

Vui lòng lưu ý các dấu ngoặc nhọn trong ảnh chụp màn hình bên dưới – đó là dấu hiệu rõ ràng nhất về công thức mảng trong Excel:

Công thức 3. Hàm SUMPRODUCT

Một cách khác để đếm các ô có logic OR trong Excel là sử dụng Hàm SUMPRODUCT theo cách này:

SUMPRODUCT (1 * (phạm vi = {tiêu chí1, tiêu chí2, tiêu chí3, Lọ}))

Để hình dung rõ hơn logic, điều này cũng có thể được viết là:

SUMPRODUCT ((phạm vi= =tiêu chí1) + (phạm vi= =tiêu chí2) + ‘)

Công thức kiểm tra từng ô trong phạm vi theo từng tiêu chí và trả về TRUE nếu tiêu chí được đáp ứng, FALSE nếu không. Kết quả trung gian, bạn nhận được một vài mảng giá trị TRUE và FALSE (số lượng mảng bằng số lượng tiêu chí của bạn). Sau đó, các phần tử mảng trong cùng một vị trí được thêm vào với nhau, tức là các phần tử đầu tiên trong tất cả các mảng, các phần tử thứ hai, v.v. Hoạt động bổ sung chuyển đổi các giá trị logic thành số, do đó, bạn kết thúc với một mảng 1 (một trong các tiêu chí khớp) và 0 (không có tiêu chí nào phù hợp). Bởi vì tất cả các tiêu chí được kiểm tra đối với cùng một ô, không có cách nào bất kỳ số nào khác có thể xuất hiện trong mảng kết quả – chỉ một mảng ban đầu có thể có TRUE ở một vị trí cụ thể, các số khác sẽ có FALSE. Cuối cùng, SUMPRODUCT cộng các phần tử của mảng kết quả và bạn có được số lượng mong muốn.

Công thức đầu tiên hoạt động theo cách tương tự, với sự khác biệt là nó trả về một mảng 2 chiều của các giá trị TRUE và FALSE, mà bạn nhân với 1 để chuyển đổi các giá trị logic thành 1 và 0, tương ứng.

Áp dụng cho tập dữ liệu mẫu của chúng tôi, các công thức có dạng như sau:

=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))

Hoặc là

=SUMPRODUCT((A2:A10="apples") + (A2:A10="bananas") + (A2:A10="lemons"))

Thay thế hằng số mảng được mã hóa cứng bằng tham chiếu phạm vi và bạn sẽ nhận được ngay cả một giải pháp thanh lịch hơn:

=SUMPRODUCT(1*( A2:A10=F1:H1))

Ghi chú. Hàm SUMPRODUCT chậm hơn COUNTIF, đó là lý do tại sao công thức này được sử dụng tốt nhất trên các tập dữ liệu tương đối nhỏ.

Đếm các ô có OR cũng như logic AND

Khi làm việc với các tập dữ liệu lớn có mối quan hệ đa cấp và chéo giữa các yếu tố, rất có thể bạn sẽ cần phải đếm các ô có điều kiện OR và AND tại một thời điểm.

Ví dụ: chúng ta hãy lấy một số “táo”, “chuối” và “chanh” được “giao”. làm sao chúng ta làm việc đó bây giờ? Để bắt đầu, hãy dịch điều kiện của chúng tôi sang ngôn ngữ của Excel:

• Cột A: “táo” hoặc “chuối” hoặc “chanh”
• Cột C: “đã giao”

Nhìn từ một góc độ khác, chúng ta cần đếm các hàng với “táo và giao” HOẶC “chuối và giao” HOẶC “chanh và giao”. Đặt theo cách này, tác vụ sẽ chuyển sang đếm các ô có 3 điều kiện HOẶC – chính xác là những gì chúng ta đã làm trong phần trước! Sự khác biệt duy nhất là bạn sẽ sử dụng COUNTIFS thay vì COUNTIF để đánh giá tiêu chí AND trong mỗi điều kiện OR.

Công thức 1. Hàm COUNTIFS + COUNTIFS

Đây là công thức dài nhất, dễ viết nhất 🙂

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") + COUNTIFS(A2:A10, "bananas", C2:C10, "delivered")) + COUNTIFS(A2:A10, "lemons", C2:C10, "delivered"))

Ảnh chụp màn hình bên dưới hiển thị cùng một công thức với các tham chiếu ô:

=COUNTIFS(A2:A10, K1, C2:C10, K2) + COUNTIFS(A2:A10, L1, C2:C10, K2) + COUNTIFS(A2:A10, M1,C2:C10, K2)

Công thức 2. Hàm COUNTIFS với hằng số mảng

Một công thức COUNTIFS nhỏ gọn hơn với logic AND / OR có thể được tạo bằng cách đóng gói các tiêu chí HOẶC trong một hằng số mảng:

=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))

Khi sử dụng tham chiếu phạm vi cho các tiêu chí, bạn cần một công thức mảng, hoàn thành bằng cách nhấn Ctrl + Shift + Enter:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))

Chú ý. Nếu cần, bạn có thể tự do sử dụng ký tự đặc biệt trong các tiêu chí của bất kỳ công thức thảo luận ở trên. Ví dụ: để đếm tất cả các loại chuối như “chuối xanh” hoặc “chuối goldfinger”, bạn có thể sử dụng công thức này:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered"))

Theo cách tương tự, bạn có thể xây dựng một công thức để đếm các ô dựa trên các loại tiêu chí khác. Ví dụ: để có được số lượng “táo” hoặc “chuối” hoặc “chanh” được “giao” và số tiền lớn hơn 200, hãy thêm một cặp tiêu chí / cặp tiêu chí vào COUNTIFS:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered", B2:B10, ">200"))

Hoặc, sử dụng công thức mảng này (được nhập qua Ctrl + Shift + Enter):

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2, B2:B10, ">"&F3))

Đếm các ô có nhiều điều kiện OR

Trong ví dụ trước, bạn đã học cách kiểm tra một tập các điều kiện OR. Nhưng điều gì sẽ xảy ra nếu bạn có hai hoặc nhiều bộ và bạn đang tìm cách để có được tổng cộng tất cả các mối quan hệ HOẶC có thể?

Tùy thuộc vào số lượng điều kiện bạn cần xử lý, bạn có thể sử dụng COUNTIFS với hằng số mảng hoặc TỔNG HỢP với MATN ISBER. Cái trước tương đối dễ xây dựng, nhưng nó chỉ giới hạn trong 2 bộ điều kiện OR. Cái sau có thể đánh giá bất kỳ số điều kiện nào (tất nhiên là một số hợp lý, đưa ra giới hạn của Excel cho 255 đối số và 8192 ký tự cho tổng chiều dài công thức), nhưng có thể phải mất một số nỗ lực để nắm bắt logic của công thức.

Đếm các ô với 2 bộ điều kiện OR

Khi chỉ xử lý hai bộ tiêu chí OR, chỉ cần thêm một mảng không đổi vào công thức COUNTIFS đã thảo luận ở trên.

Để công thức hoạt động, cần một phút nhưng thay đổi quan trọng: sử dụng một mảng ngang (các yếu tố được phân tách bằng dấu phẩy) cho một bộ tiêu chí và mảng dọc (các phần tử được phân tách bằng dấu chấm phẩy) cho phần kia. Điều này bảo Excel “ghép” hoặc “tính toán chéo” các phần tử trong hai mảng và trả về một mảng hai chiều của kết quả.

Ví dụ: hãy đếm “táo”, “chuối” hoặc “chanh” được “giao” hoặc “quá cảnh”:

=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))

Xin lưu ý dấu chấm phẩy trong hằng số mảng thứ hai:

Vì Excel là chương trình 2 chiều, nên không thể xây dựng mảng 3 chiều hoặc 4 chiều, và do đó công thức này chỉ hoạt động cho hai bộ tiêu chí OR. Để tính nhiều tiêu chí hơn, bạn sẽ phải chuyển sang công thức SUMPRODVEL phức tạp hơn được giải thích trong ví dụ tiếp theo.

Đếm các ô có nhiều bộ điều kiện OR

Để đếm các ô có nhiều hơn hai bộ tiêu chí OR, hãy sử dụng hàm SUMPRODVEL cùng với MATN ISBER.

Ví dụ: hãy lấy số lượng “táo”, “chuối” hoặc “chanh” được “giao” hoặc “quá cảnh” và được đóng gói trong “túi” hoặc “khay”:

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))* ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))* ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

Trong phần trung tâm của công thức, hàm MATCH kiểm tra các tiêu chí bằng cách so sánh từng ô trong phạm vi được chỉ định với hằng số mảng tương ứng. Nếu tìm thấy kết quả khớp, nó sẽ trả về vị trí tương đối của giá trị nếu mảng, N / A khác. ISNUMBER chuyển đổi các giá trị này thành TRUE và FALSE, tương ứng với 1 và 0, tương ứng. TÓM TẮT lấy nó từ đó và nhân các phần tử của mảng. Bởi vì nhân với số 0 sẽ cho số không, chỉ các ô có 1 trong tất cả các mảng tồn tại và được tính tổng.

Ảnh chụp màn hình bên dưới cho thấy kết quả:

Đây là cách bạn sử dụng các hàm COUNTIF và COUNTIFS trong Excel để đếm các ô có nhiều điều kiện AND cũng như OR. Để xem kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống mẫu của Excel COUNTIF HOẶC / VÀ sách bài tập. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của Dịch vụ kế toán tại Hà Nội vào tuần tới!