Countifs với logic HOẶC cho nhiều tiêu chí trong Excel
Thông thường, bạn có thể sử dụng hàm COUNTIFS để đếm ô dựa trên một hoặc nhiều điều kiện với logic AND trong Excel. Bạn đã bao giờ gặp phải trường hợp cần đếm nhiều hơn một giá trị từ một cột hoặc một phạm vi ô chưa? Điều này có nghĩa là đếm với nhiều điều kiện và logic HOẶC. Trong trường hợp này, bạn có thể áp dụng hàm SUM và COUNTIFS cùng nhau hoặc sử dụng hàm SUMPRODUCT.
Đếm ô có điều kiện OR trong Excel
Ví dụ: tôi có một loạt dữ liệu như hình minh họa bên dưới, bây giờ, tôi muốn đếm số lượng sản phẩm là “Bút chì” hoặc “Thước kẻ”, ở đây, tôi sẽ thảo luận về hai công thức để giải công việc này trong Excel.
Đếm các ô có điều kiện OR bằng cách sử dụng hàm SUM và COUNTIFS
Trong Excel, để đếm với nhiều điều kiện OR, bạn có thể sử dụng các hàm SUM và COUNTIFS với một hằng số mảng, cú pháp chung là:
- range: Phạm vi dữ liệu chứa các tiêu chí mà bạn đếm các ô từ đó;
- criterion1, criterion2, criterion3…: Các điều kiện bạn muốn đếm ô dựa trên.
Để đếm số lượng sản phẩm là “Bút chì” hoặc “Thước kẻ”, vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống, sau đó nhấn đăng ký hạng mục thi phím để nhận kết quả:
Giải thích công thức:
= SUM (COUNTIFS (B2: B13, {"Bút chì", "Thước kẻ"}))
- {"Bút chì", "Thước kẻ"}: Đầu tiên, bạn nên đóng gói tất cả các điều kiện trong một hằng số mảng như sau: {"Pencil", "Ruler"}, phân tách các mục bằng dấu phẩy.
- COUNTIFS (B2: B13, {"Bút chì", "Thước kẻ"}): Hàm COUNTIFS này sẽ nhận các số đếm riêng lẻ cho “Bút chì” và “Thước kẻ” và bạn sẽ nhận được kết quả như sau: {2,3}.
- SUM (COUNTIFS (B2: B13, {"Pencil", "Ruler"})) = SUM ({2,3}): Cuối cùng, hàm SUM này tính tổng tất cả các mục trong mảng và trả về kết quả: 5.
Lời khuyên: Bạn cũng có thể sử dụng tham chiếu ô cho tiêu chí, vui lòng áp dụng công thức mảng bên dưới, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận được kết quả chính xác:
Đếm các ô có điều kiện OR bằng cách sử dụng hàm SUMPRODUCT
Đây là một công thức khác được tạo bởi hàm SUMPRODUCT có thể giúp đếm các ô bằng logic OR. Cú pháp chung là:
- range: Phạm vi dữ liệu chứa các tiêu chí mà bạn đếm các ô từ đó;
- criterion1, criterion2, criterion3…: Các điều kiện bạn muốn đếm ô dựa trên.
Vui lòng sao chép hoặc nhập công thức sau vào một ô trống và nhấn đăng ký hạng mục thi phím để trả về kết quả:
Giải thích công thức:
= SUMPRODUCT (1 * (B2: B13 = {"Bút chì", "Thước kẻ"}))
- B2: B13 = {"Bút chì", "Thước kẻ"}: Biểu thức này so sánh từng tiêu chí “Bút chì”, “Thước kẻ” với ô phạm vi B2: B13. Nếu tiêu chí được đáp ứng, nó sẽ trả về TRUE, ngược lại, FALSE sẽ hiển thị, bạn sẽ nhận được kết quả như sau: {TRUE, FALSE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; TRUE, FALSE ; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE; FALSE, FALSE; FALSE, TRUE; FALSE, FALSE}.
- 1 * (B2: B13 = {"Bút chì", "Thước kẻ"}): Phép nhân chuyển đổi các giá trị logic - TRUE và FALSE thành 1 và 0, vì vậy nó sẽ nhận được kết quả như sau: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Cuối cùng, hàm SUMPRODUCT cộng tất cả các số trong mảng để nhận được kết quả: 5.
Đếm ô có nhiều bộ điều kiện OR trong Excel
Đôi khi, bạn có thể cần đếm các ô có hai hoặc nhiều bộ điều kiện HOẶC, trong trường hợp này, bạn có thể sử dụng SUM và COUNTIFS với hằng số mảng hoặc SUMPRODUCT với hàm ISNUMBER MATCH.
Đếm ô có hai bộ điều kiện OR bằng cách sử dụng hàm SUM và COUNTIFS
Để đối phó với chỉ hai bộ tiêu chí OR, bạn chỉ cần thêm một hằng số mảng khác vào công thức COUNTIFS.
Ví dụ: tôi có phạm vi dữ liệu như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn đếm những người đã đặt hàng “Bút chì” hoặc “Thước kẻ” và Số tiền là <100 hoặc> 200.
Vui lòng nhập hoặc sao chép công thức sau vào một ô, sau đó nhấn đăng ký hạng mục thi để lấy kết quả, xem ảnh chụp màn hình:
Chú thích: Trong công thức, bạn nên sử dụng dấu chấm phẩy cho hằng số mảng thứ hai, hằng số này tạo ra một mảng dọc.
Đếm các ô có nhiều bộ điều kiện HOẶC bằng cách sử dụng hàm SUMPRODUCT
Công thức trên chỉ hoạt động với hai bộ tiêu chí HOẶC, nếu bạn cần đếm với nhiều tiêu chí hơn, công thức SUMPRODUCT phức tạp cùng với hàm ISNUMBER MATCH có thể giúp bạn.
Lấy dữ liệu dưới đây làm ví dụ: để đếm những người đã đặt hàng "Bút chì" hoặc "Thước kẻ" và Trạng thái là "Đã giao" hoặc "Đang chuyển" và được ký bởi "Bob" hoặc "Eko", bạn nên áp dụng một công thức phức tạp .
Vui lòng sao chép hoặc nhập công thức dưới đây vào một ô trống, sau đó nhấn đăng ký hạng mục thi phím để tính toán, xem ảnh chụp màn hình:
Giải thích công thức:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER (TRẬN ĐẤU (B2: B13, {"Pencil", "Ruler"}, 0)):
- TRẬN ĐẤU (B2: B13, {"Bút chì", "Thước kẻ"}, 0): Hàm MATCH này được sử dụng để so sánh từng ô trong phạm vi B2: B13 với hằng số mảng tương ứng. Nếu tìm thấy kết quả phù hợp, nó sẽ trả về một vị trí tương đối của giá trị trong mảng, nếu không sẽ hiển thị giá trị lỗi. Vì vậy, bạn sẽ nhận được danh sách mảng như sau: {1; # N / A; # N / A; 2; # N / A; 1; # N / A; 2; 1; # N / A; 2; # N / A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Hàm ISNUMBER chuyển đổi các số thành TRUE và giá trị lỗi thành FALSE như sau: {TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
Logic ở trên cũng có thể được áp dụng cho biểu thức ISNUMBER thứ hai và thứ ba.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- Sau đó, ba mảng này được nhân với nhau bên trong SUMPRODUCT, tự động chuyển đổi các giá trị TRUE và FALSE thành 1s và 0s như một phần của phép toán như sau: SUMPRODUCT ({1; 0; 0; 1; 0; 1; 0; 1; 1; 0; 1; 0} * {1; 1; 0; 0; 1; 1; 0; 1; 1; 0; 1; 1} * {1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}) = SUMPRODUCT ({1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0}).
- Cuối cùng, hàm SUMPRODUCT tính tổng tất cả các số trong mảng để nhận được kết quả: 3.
Hàm tương đối được sử dụng:
- TỔNG:
- Hàm SUM trong Excel trả về tổng giá trị được cung cấp.
- Countif:
- Hàm COUNTIF là một hàm thống kê trong Excel được sử dụng để đếm số ô đáp ứng một tiêu chí.
- GIỚI THIỆU:
- Hàm SUMPRODUCT có thể được sử dụng để nhân hai hoặc nhiều cột hoặc mảng với nhau, sau đó lấy tổng các tích.
- THÁNG NĂM:
- Hàm ISNUMBER trong Excel trả về TRUE khi ô chứa một số và FALSE nếu không.
- TRẬN ĐẤU:
- Hàm MATCH của Microsoft Excel tìm kiếm một giá trị cụ thể trong một dải ô và trả về vị trí tương đối của giá trị này.
Các bài viết khác:
- Đếm giá trị số duy nhất dựa trên tiêu chí
- Trong trang tính Excel, bạn có thể gặp phải sự cố đếm số lượng giá trị số duy nhất dựa trên một điều kiện cụ thể. Ví dụ: làm cách nào để tôi có thể đếm các giá trị Qty duy nhất của sản phẩm “áo phông” từ báo cáo như hình minh họa bên dưới? Trong bài viết này, tôi sẽ hiển thị một số công thức để đạt được nhiệm vụ này trong Excel.
- Đếm các ô phù hợp với hai hoặc nhiều tiêu chí
- Trong Excel, để đếm số ô dựa trên hai hoặc nhiều tiêu chí, hàm COUNTIFS có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.
- Đếm số hàng với nhiều tiêu chí HOẶC
- Để đếm số hàng có nhiều tiêu chí trong các cột khác nhau, với logic HOẶC, hàm SUMPRODUCT có thể giúp bạn. Ví dụ: tôi có một báo cáo sản phẩm như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn đếm các hàng có sản phẩm là “Áo phông” hoặc màu là “Đen”. Làm thế nào để đối phó với nhiệm vụ này trong Excel?
Các công cụ năng suất văn phòng tốt nhất
Kutools cho Excel - Giúp bạn nổi bật giữa đám đông
Kutools cho Excel tự hào có hơn 300 tính năng, Đảm bảo rằng những gì bạn cần chỉ là một cú nhấp chuột...
Tab Office - Bật tính năng Đọc và Chỉnh sửa theo Tab trong Microsoft Office (bao gồm Excel)
- Một giây để chuyển đổi giữa hàng chục tài liệu đang mở!
- Giảm hàng trăm cú click chuột cho bạn mỗi ngày, tạm biệt bàn tay chuột.
- Tăng năng suất của bạn lên 50% khi xem và chỉnh sửa nhiều tài liệu.
- Mang các tab hiệu quả đến Office (bao gồm Excel), giống như Chrome, Edge và Firefox.
