Có 5 loại hàm đếm được sử dụng cho các điều kiện khác nhau, cụ thể như sau:
Tên hàm Giải thích COUNT Hàm đếm các ô có chứa số COUNTIF Hàm đếm theo một điều kiện COUNTIFS Hàm đếm theo nhiều điều kiện COUNTA Hàm đếm các ô không trống
COUNTBLANK Hàm đếm các ô trống
Xem thêm: Hàm SUMIF
Hàm COUNTIF được dùng để đếm số ô thỏa mãn điều kiện [criteria] trong một vùng dữ liệu được chọn [range].
Cú pháp hàm COUNTIF:
Ví dụ:
Bạn có bảng thống kê các mặt hàng và số lượng tồn tương ứng như hình dưới.
Để thống kê xem có bao nhiêu mặt hàng còn tồn trên 150 sản phẩm, bạn nhập công thức:
=COUNTIF[C2:C11,">150"]
Kết quả trả về cho thấy có tổng cộng 8 mặt hàng còn tồn trên 150 sản phẩm.
Lưu ý:
- Có thể dùng các ký tự đại diện trong điều kiện: Ví dụ: Dấu ? đại diện cho một ký tự bất kỳ "?ĐH?", dấu * đại diện cho nhiều ký tự bất kỳ "ĐH*".
- Để tìm điều kiện là những dấu ? hoặc * thì bạn hãy gõ thêm dấu ~ ở trước dấu ? hay *. [Ví dụ: "~?", "~*"]
- Hàm COUNTIF không phân biệt chữ hoa/chữ thường.
Hàm COUNTIFS được dùng để đếm số ô thỏa mãn nhiều điều kiện [criteria] trong vùng dữ liệu được chọn [range].
Cú pháp hàm COUNTIFS:
Lưu ý: Mỗi ô trong vùng dữ liệu để đếm chỉ được đếm nếu tất cả các điều kiện tương ứng với ô đó đều đúng.
Ví dụ:
Bạn có bảng thống kê các mặt hàng và số lượng tồn tương ứng như hình dưới.
Để thống kê xem có bao nhiêu mặt hàng tại kho 1 còn tồn trên 150 sản phẩm, bạn nhập công thức:
=COUNTIF[C2:C11,">150",D2:D11,"1"]
Kết quả trả về cho thấy có tổng cộng 2 mặt hàng tại kho 1 còn tồn trên 150 sản phẩm.
Hàm COUNTA được sử dụng để đếm các ô không trống [các ô chứa giá trị chuỗi và số] trong một vùng dữ liệu nhất định.
Cú pháp hàm COUNTA:
Ví dụ:
Bạn có một bảng danh sách thống kê tình trạng nộp bài của sinh viên như hình dưới. Bây giờ, bạn muốn biết số lượng sinh viên đã nộp bài [Đã có link bài tập].
Bạn nhập công thức: =COUNTA[C2:C15]. Kết quả trả về hiển thị có tổng cộng 6 sinh viên đã nộp bài tập.
Hàm COUNTBLANK được dùng để đếm các ô trống [ô rỗng] trong một vùng được chọn.
Cú pháp hàm COUNTBLANK:
Ví dụ:
Bạn có một bảng danh sách thống kê tình trạng nộp bài của sinh viên. Bây giờ, bạn muốn biết số lượng sinh viên chưa nộp bài [Chưa có link bài tập].
Bạn nhập công thức: =COUNTBLANK[C2:C15]. Kết quả trả về hiển thị có tổng cộng 8 sinh viên chưa nộp bài tập.
Dưới đây là một số điều bạn nên nắm khi dùng hàm đếm trong Excel:
- Hàm COUNTIF không phân biệt chữ hoa, chữ thường trong chuỗi văn bản. Ví dụ, chuỗi "học" và chuỗi "HỌC" sẽ khớp với cùng các ô giống nhau.
- Khi dùng hàm COUNTIF[S], có thể dùng ký tự đại diện là dấu chấm hỏi [?] và dấu sao [*] trong criteria.
- Một dấu chấm hỏi [?] biểu thị cho một ký tự đơn lẻ bất kỳ. Ví dụ:
- Một dấu sao [*] biểu thị cho chuỗi ký tự bất kỳ. Ví dụ:
- Để tìm dấu chấm hỏi [?] hay dấu sao [*] thực sự, hãy nhập một dấu sóng [~] trước ký tự đó.
- Khi đếm giá trị văn bản, hãy đảm bảo dữ liệu không chứa khoảng trắng ở đầu và ở cuối.
- Nên sử dụng phạm vi có tên: COUNTIF hỗ trợ các phạm vi có tên trong công thức. Cách đặt tên cho phạm vi như sau:
Bạn chọn vùng muốn đặt tên > Nhấn chuột phải chọn Define name > Gõ tên của vùng vào ô Name như hình dưới rồi nhấn OK.
Sau khi đặt tên, bạn có thể thay thế phạm vi thành tên của phạm vi đó trong công thức. Ví dụ:
Lưu ý: Phạm vi có tên có thể nằm trong trang tính hiện tại, một trang tính khác trong cùng sổ làm việc hoặc từ sổ làm việc khác. Để tham chiếu từ một sổ làm việc khác, sổ làm việc thứ hai đó cũng phải được mở.
Tham khảo một số mẫu laptop kinh doanh tại Điện máy XANH:
Xem thêm:
Trên đây là hướng dẫn cách dùng hàm đếm trong Excel. Hy vọng bài viết sẽ giúp ích cho bạn trong việc thao tác với dữ liệu nhé!
Trong bài viết này, Học Excel Online sẽ hướng dẫn cách sử dụng công thức Hàm COUNTIFS và sử dụng Hàm COUNTIF trong Excel dựa trên thuật logic AND và OR. Bạn sẽ tìm thấy rất nhiều ví dụ với các kiểu dữ liệu khác nhau – số, ngày tháng, text và ô chứa dữ liệu.
Xem thêm:
Hàm COUNTIFS – công thức và cách sử dụng
Công thức hàm COUNTIFS như sau:
=COUNTIFS [criteria_range1, criteria1, [criteria_range2, criteria2]…]
criteria_range1 – xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên [criteria1], bắt buộc.
criteria1 – đặt điều kiện cho dạng số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác, bắt buộc. Tiêu chí này xác định những ô tính nào sẽ được đếm và có thể biểu diễn là 10, “ 0”, C2: C7, “= 0”]
Kết quả nhận được là 2 [“Cherries” và “Lemons”]:
Công thức 2. Công thức COUNTIFS với các tiêu chí giống nhau
Khi bạn muốn đếm các mục có các tiêu chí giống nhau, bạn vẫn phải cung cấp mỗi cặp criteria_range / criteria một cách riêng lẻ.
Ví dụ: đây là công thức đúng để đế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”]
Công thức COUNTIFS này trả về 1 vì chỉ có “Grapes” có giá trị “0” trong cả hai cột.
Sử dụng một công thức đơn giản hơn với một criteria_range duy nhất như COUNTIFS[B2: C7, “= 0”] sẽ mang lại kết quả khác – tổng số các ô tính trong khoảng B2:C7 chứa 0 [trong ví dụ này là 4].
Xem thêm: Tổng hợp kiến thức về Excel nâng cao
Cách đếm số ô tính với nhiều tiêu chí [OR logic]:
Như bạn đã thấy trong ví dụ trên, việc đếm các ô tính đáp ứng tất cả các tiêu chí chỉ định rất dễ dàng bởi vì hàm COUNTIFS được thiết kế để hoạt động theo cách này.
Nhưng nếu bạn muốn đếm các ô có ít nhất một trong các tiêu chí được chỉ định là TRUE, nghĩa là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực điều này – bằng cách thêm một số 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
Trong bảng bên dưới, giả sử bạn muốn tính các đơn đặt hàng với trạng thái “Cancelled” và “Pending“, bạn bạn chỉ 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”]
Trong trường hợp mỗi hàm chứa nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: để đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, sử dụng công thức sau:
=COUNTIFS[$A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”]+COUNTIFS[$A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”]
Công thức 2. Công thức SUM COUNTIFS với hằng mảng
Trong tình huống bạn cần thiết ập nhiều tiêu chí, cách tiếp cận trên không phải là cách tối ưu bởi công thức của bạn sẽ quá dài dòng. Để thực hiện các phép tính tương tự trong một công thức nhỏ gọn hơn, hãy liệt kê tất cả các tiêu chí của bạn trong mảng và cung cấp mảng đó cho criteria của hàm COUNTIFS. Để đếm tổng, kết hợp hàm COUNTIFS bên trong hàm SUM như sau:
=SUM [COUNTIFS [range, {“criteria1”, “criteria2”, “criteria3”, …}]]
Trong bảng mẫu của chúng tôi, để đếm các đơn đặt hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, công thức sẽ như sau:
=SUM[COUNTIFS[$C$2:$C$11,{“Cancelled”,”Pending”,”In Transit”}]]
Tương tự, bạn có thể đếm các ô dựa trên hai hoặc nhiều hơn các cặp criteria_range/criteria. Ví dụ: để biết số lượng đơn đặt hàng “Apples” trong trạng thái “Cancelled”, “Pending”, “In transit”, sử dụng công thức này:
=SUM[COUNTIFS[$A$2:$A$11,”Apples”,$C$2:$C$11,{“Cancelled”,”Pending”,”In transit”}]]
Cách đếm số trong khoảng cụ thể:
Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số
Để tìm ra có bao nhiêu số giữa 5 và 10 [không bao gồm 5 và 10] được chứa trong các ô C2 đến C10, sử dụng công thức này:
=COUNTIFS[C2:C10,”>5″,C2:C10,”=5″,B2:B10,”5″]-COUNTIF[C2:C10,”=10″] – đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong vùng dữ liệu C2:C10.
=COUNTIF[C2:C10,”>=5″]-COUNTIF[C2:C10,”>10″] – công thức đếm có bao nhiêu số trong đoạn từ 5 đến 10 thuộc vùng dữ iệu C2:C10, bao gồm cả 5 và 10.
Cách sử dụng tham chiếu ô trong công thức COUNTIFS
Khi sử dụng các toán tử logic như “>”, “200″]
Hoặc, bạn có thể nhập các giá trị tiêu chí và những ô nhất định, ví dụ F1 và F2, sau đó tham chiếu những ô đó trong công thức của bạn:
=COUNTIFS[$A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2]
Chú ý 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.
Xem thêm: Khoá học Excel từ cơ bản đến nâng cao
Cách sử dụng COUNTIFS với các ký tự đại diện:
Trong công thức Excel, bạn có thể sử dụng các ký tự đại diện sau đây:
Dấu hỏi [?] – khớp với bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.
Dấu hoa thị [*] – khớp với bất kỳ dãy ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.
Lưu ý. Nếu bạn muốn đếm các ô chứa dấu hỏi hoặc dấu hoa thị, gõ dấu ngã [~] trước dấu hoa thị hoặc dấu chấm hỏi.
Giả sử bạn có danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết, tức là cột B có chứa tên người. Hãy thêm một điều kiện thứ hai – End date trong cột D.
=COUNTIFS[B2:B10,”*”,D2:D10,””&””]]
Lưu ý, bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 vì cột D chứa kiểu dữ liệu ngày tháng chứ không phải dạng text. Đó là lý do tại sao bạn sử dụng tiêu chí “”&”” để tìm ô chứa dữ liệu.
COUNTIFS và COUNTIF với nhiều tiêu chí ngày tháng:
Ví dụ 1. Đếm ngày trong một khoảng thời gian cụ thể
Để đếm ngày trong một khoảng thời gian cụ thể, bạn có thể sử dụng công thức COUNTIFS với hai tiêu chí hoặc kết hợp hai hàm COUNTIF với nhau.
Ví dụ, các công thức sau đây đếm số ngày trong các ô C2 đến C10, khoảng thời gian từ ngày 1/6/2014 đến 7/7/2014 bao gồm:
=COUNTIFS[C2:C9,”=6/1/2014″,C2:C9,”5/1/2014″,D2:D9,”6/7/2014″]
Ví dụ 3. Đếm ngày với nhiều điều kiện dựa trên ngày hiện tại
Bạn có thể sử dụng hàm TODAY kết hợp với COUNTIF để tính các ngày dựa trên ngày hôm nay.
Ví dụ: công thức COUNTIF sau với hai vùng dữ liệu và hai tiêu chí sẽ cho bạn biết có bao nhiêu sản phẩm đã được mua nhưng chưa giao hàng:
=COUNTIFS[C2:C9,””&TODAY[]]
Công thức này có thể chứa nhiều biến. Ví dụ: bạn có thể tinh chỉnh nó để đếm có bao nhiêu sản phẩm được mua hơn một tuần trước và chưa giao hàng:
=COUNTIFS[C2:C9,””&TODAY[]]
Như vậy với bài viết này, blog.hocexecl.online đã chia sẻ cũng như hướng dẫn cho các bạn một cách sử dụng công thức CountIFS và CountIF trong Excel một cách chi tiết nhất, nếu có bất kỳ thắc mắc nào các bạn hãy comment ngay ở bên dưới bài viết để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Học Excel Online. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Hãy tham gia ngay tại địa chỉ: Học Excel Online