Hàm COUNTIFS của Excel được dùng để đếm các ô trong Excel. Hàm đếm có điều kiện COUNTIFS và COUNTIF thường dễ bị nhầm lẫn nhất vì trông rất giống nhau dùng để đếm các ô dựa trên các điều kiện đã được chỉ định. Dưới đây là hướng dẫn cách dùng hàm đếm có điều kiện trong Excel COUNTIFS và COUNTIF.
Hàm đếm có điều kiện COUNTIFS và COUNTIF thường dễ bị nhầm lẫn nhất vì ựa trên thuật logic AND và OR. Có mục đích dùng để đếm các ô dựa trên các điều kiện đã được chỉ định. Hàm COUNTIFS và Hàm COUNTIF trong Excel khác biệt duy nhất giữa hai hàm này là COUNTIF để đếm các ô có một điều kiện trong một phạm vi. COUNTIFS có thể đánh giá các điều kiện khác nhau trong các phạm vi khác nhau. Bạn sẽ tìm thấy rất nhiều ví dụ với các thiết kế kiểu dữ liệu khác nhau – số, ngày tháng, text. Cú pháp của hàm COUNTIF cũng khá đơn giản các bạn chỉ sử dụng 1-2 lần sẽ biết cách áp dụng.
Thường thì hàm COUNTIF yêu cầu đếm số ô chứa giá trị thỏa mãn điều kiện cho sẵn dùng với những bảng dữ liệu thống kê.
Hàm COUNTIFS phức tạp hơn hàm COUNTIF, COUNTIFS hỗ trợ nhiều điều kiện và phạm vi so với COUNTIF. Hàm COUNTIFS không chỉ đơn giản là tính tổng kết quả của các ô cho mỗi phạm vi ô. Nếu bạn có hai điều kiện cho hai phạm vi là hàm COUTNIFS sẽ trả về các giá trị đáp ứng cả hai điều kiện qua điều kiện đầu tiên và sau đó qua điều kiện thứ hai.
Hàm COUNTIFS của Excel có sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007 được dùng để đếm các ô trên nhiều phạm vi mà nó dựa trên một số điều kiện khác nhau
Cú pháp: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Cụ thể:
Microsoft Excel sẽ hiển thị các đối số bạn đang nhập được tô đậm. Không cần phải nhớ cú pháp của hàm đếm có điều kiện COUNTIFS. Thực tế, bạn chỉ cần nhớ sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi một điều kiện cũng như trong nhiều phạm vi với nhiều điều kiện. Bạn có thể sử dụng hàm COUNTIFS trong Excel trong trường hợp tất cả các điều kiện được chỉ định mới được tính.
Bạn có thể sử dụng các ký tự đại diện trong điều kiện – dấu hoa thị (*) và dấu hỏi (?).
Với Criteria1 bạn có thể sử dụng 10, “<=32”, A4, “gord”…
Mỗi phạm vi bổ sung phải có cùng số hàng và cột phạm vi tiếp giáp và không liền kề đều được cho phép.
Công thức 1. Công thức COUNTIFS với các tiêu chí khác nhau
Công thức 2. Công thức COUNTIFS với các tiêu chí giống nhau
Ví dụ: đếm các mục chứa 0 cả trong cột B và cột C:
= COUNTIFS ($B$2: $B$7, “= 0”, $C$2: $C$7, “= 0”)
Sử dụng công thức đơn giản một criteria_range duy nhất như COUNTIFS(B2: C7, “= 0”)
Việc đếm các ô tính đáp ứng tất cả các tiêu chí, một trong các tiêu chí được chỉ định là TRUE. Sử dụng công thức COUNTIF hoặc sử dụng công thức SUM COUNTIFS với hằng số mảng.
Công thức 1. Thêm hai hoặc nhiều công thức COUNTIF hoặc COUNITFS
Ví dụ bạn muốn tính các đơn đặt hàng với trạng thái “Cancelled” và “Pending“, bạn có thể viết 2 công thức COUNTIF bình thường và nhận kết quả:
=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)
Công thức 2. Công thức SUM COUNTIFS với hằng mảng
Nếu như bạn muốn thiết lập nhiều tiêu chí, cách dùng công thức trên sẽ không tối ưu vì quá dài dòng. Bạn thực hiện các phép tính trong một công thức nhỏ gọn hơn, cung cấp mảng đó cho criteria của hàm COUNTIFS.
Để đếm tổng, bạn thực hiện kết hợp hàm COUNTIFS bên trong hàm SUM như sau:
=SUM (COUNTIFS (range, {“criteria1”, “criteria2”, “criteria3”, …}))
Trong Excel, bạn có thể sử dụng COUNTIFS với các ký tự đại diện như sau:
– ? : được sử dụng để đếm các ô bắt đầu hoặc được sử dụng để đếm các ô kết thúc bằng một số ký tự nhất định.
– * ” Được sử dụng để đếm ô chứa một từ cụ thể hoặc được sử dụng để đếm một ký tự trong ô.
=COUNTIFS(B2:B10,”*”,D2:D10,”<>”&””))
Lưu ý, bạn sử dụng tiêu chí “<>”&”” để tìm ô chứa dữ liệu.
Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số
Sử dụng công thức này:
=COUNTIFS(C2:C10,”>5″,C2:C10,”<10″)
=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)
Công thức 2. Công thức COUNTIF để đếm số giữa X và Y
Chúng ta có thể sử dụng công thức COUNTIF đếm những số lớn hơn giá trị giới hạn dưới 5, loại bỏ những số lớn hơn giá trị giới hạn trên 10.
=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)
Khi sử dụng các ký hiệu logic như “>”, “<“, “<=” hoặc “>=” cùng với các tham chiếu ô trong công thức COUNTIFS, bạn cần đặt các toán tử vào ngoặc kép và thêm dấu (&) trước tham chiếu ô.
Bạn có thể sử dụng công thức sau đó tham chiếu những ô đó trong công thức của bạn:
=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)
=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)
Chúng ta sẽ sử dụng giá trị tuyệt đối trong criteria và criteria_range để công thức đó vẫn đúng khi sao chép sang các ô tính khác.
Để đếm các ô đã lọc có nội dung khớp với chuỗi văn bản mẫu sử dụng một trong các công thức sau: F1 là văn bản mẫu và B2: B10 là các ô cần đếm.
= SUMPRODUCT (SUBTOTAL (103, INDIRECT (“A” & ROW (A2: A10))), – (B2: B10 = F1))
= SUMPRODUCT (SUBTOTAL (103, OFFSET (A2: A10, ROW (A2: A10) – MIN (ROW (A2: A10)),, 1)), – (B2: B10 = F1))
Đếm nội dung trong table theo nhiều điều kiện trong Excel các bạn có thể kết hợp 2 hàm: hàm Sumproduct và hàm CountIF.
Điều kiện trong hàm COUNTIFS có thể là ngày tháng, ký tự, số nên bạn có thể áp dụng linh hoạt vào việc tính toán trong bảng tính của excel sao cho đạt kết quả công việc.
Trên đây là cách dùng hàm đếm có điều kiện trong Excel và hàm đếm có điều kiện và các cách thông dụng nhất sử dụng các hàm đếm đó trong Excel. Các cách tính hàm này thông dụng nhất để bạn thực hiện, dễ dàng thống kê và phân tích số liệu hiệu quả, cải thiện được tối đa hiệu suất công việc. Bạn có thể tham khảo thêm cách dùng hàm đếm có điều kiện trong Excel trên internet để có những cách làm hiệu quả nhất.
Trong những năm gần đây, ngành Dược là một ngành học thu hút đông đảo…
Phép trừ là phép tính được sử dụng rất nhiều trong Excel. Trong bài viết…
Bạn đang gặp khó khăn về việc loại bỏ dấu trong Excel và không biết…
Bạn đang muốn tính cách phép tính bình phương khi xử lý dữ liệu trên…
Bạn đang làm việc với Excel và muốn tìm cách ẩn đi các công thức…
Hàm AND trong Excel là hàm thường xuyên được sử dụng để xử lý công…