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.
Cách sử dụng hàm COUNTIFS trong Excel
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ể:
- criteria_range1: Đó chính là vùng chứa điều kiện thứ nhất, đây là phần bắt buộc phải có
- criteria1: Điều kiện thứ nhất gồm có các dạng số, chuỗi văn bản, ô tham chiếu, hàm Excel hoặc vùng Excel khác.
- criteria_range2: điều kiện thứ hai vùng chứa criteria_range2 phải có độ lớn bằng nhau. Trong công thức của mình bạn có thể chỉ định tối đa 127 cặp phạm vi / điều kiện. Với các điều kiện khác cũng sẽ áp dụng như vậy.
Lưu ý hàm đếm có điều kiện COUNTIFS
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ách sử dụng hàm COUNTIFS và COUNTIF trong excel
Cách đếm ô tính với nhiều tiêu chí (AND logic):
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”)
Cách đếm số ô tính với nhiều tiêu chí (OR logic):
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”, …}))
Cách sử dụng COUNTIFS với các ký tự đại diện
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ách đếm số trong khoảng cụ thể
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″)
Cách sử dụng tham chiếu ô trong công thức COUNTIFS
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.