Cách dùng hàm đếm số lượng COUNTIF và COUNIFS nhiều điều kiện

Cùng xem Cách dùng hàm đếm số lượng COUNTIF và COUNIFS nhiều điều kiện trên youtube.

  • Admin
  • Bài viết

Trong bài viết này, các bạn sẽ được hướng dẫn cách sử dụng hàm đếm số lượng COUNTIF và COUNIFS 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.

CÔNG THỨC HÀM COUNTIFS

=COUNTIFS (Criteria_range1, Criteria1, [Criteria_range2, Criteria2]…)

Bạn đang xem: hàm đếm

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, “<=32”, A6, “sweets”.

[Criteria_range2, Criteria2]… – đây là các điều kiện bổ sung, tùy chọn. Bạn có thể nhập tới 127 cặp điều kiện trong công thức.

Trong thực tế, bạn không phải thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel sẽ hiển thị các đối số của hàm ngay khi bạn bắt đầu gõ. Đối số được nhập vào hiện tại sẽ được tô đậm.

hàm đếm số lượng COUNTIF và COUNIFS

COUNTIFS – NHỮNG THỨ CẦN LƯU Ý

  1. Bạn có thể sử dụng hàm COUNTIFS để đếm các ô trong một phạm vi đơn lẻ với một điều kiện duy nhất cũng như trong nhiều vùng dữ liệu với nhiều điều kiện. Trong trường hợp thứ hai, chỉ những ô tính đáp ứng tất cả các điều kiện mới được đếm.
  2. Mỗi vùng dữ liệu bổ sung phải có cùng số hàng và cột như vùng dữ liệu đầu tiên (đối số Criteria_range1).
  3. Có thể đếm những vùng dữ liệu liền kềkhông liền kề.
  4. Nếu tiêu chí là một tham chiếu đến một ô trống, hàm COUNTIFS xử lý nó như một giá trị bằng không.
  5. Bạn có thể sử dụng các Wildcard characters trong các tiêu chí – dấu hoa thị (*) và dấu chấm hỏi (?).

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

Giả sử bạn có một danh sách sản phẩm như trong hình bên dưới. Bạn muốn biết số hàng tồn kho (giá trị cột B lớn hơn 0) nhưng chưa bán được (giá trị cột C bằng 0).

Nhiệm vụ có thể được thực hiện bằng cách sử dụng công thức này:

= COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”)

Kết quả nhận được là 2 (“Cherries” và “Lemons”)

hàm đếm số lượng COUNTIF và COUNIFS

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).

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.

hàm đếm số lượng COUNTIF và COUNIFS

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 chỉ có thể viết 2 công thức COUNTIF bình thường và nhận kết quả:

Xem thêm: bus ram có tác dụng gì

Xem Thêm : mẫu đơn xin phép nghỉ dạy của giáo viên

=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

hàm đếm số lượng COUNTIF và COUNIFS

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”)

hàm đếm số lượng COUNTIF và COUNIFS

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,”<10″)

Để bao gồm cả 5 và 10, sử dụng “lớn hơn hoặc bằng” và “nhỏ hơn hoặc bằng”:

=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)

hàm đếm số lượng COUNTIF và COUNIFS

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 lồng ghép với một hàm khác khác để có kết quả tương tự. Công thức đầu tiên đếm những số lớn hơn giá trị giới hạn dưới (ví dụ là 5). Công thức thứ hai loại bỏ những số lớn hơn giá trị giới hạn trên (trong trường hợp này là 10). Kết quả bạn muốn tìm nằm trong khoảng hai giá trị nhận được trên.

=COUNTIF(C2:C10,”>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ư “>”, “<“, “<=” hoặc “>=” cùng với các tham chiếu ô trong công thức COUNTIFS, lưu ý hãy đặt các toán tử trong ngoặc kép và thêm dấu (&) trước tham chiếu ô để xây dựng một chuỗi văn bản.

Trong tập dữ liệu mẫu dưới đây, hãy tính các đơn hàng “Apples” với số tiền lớn hơn $200. Với criteria_range1 trong A2: A11 và criteria_range2 trong B2: B11, bạn có thể sử dụng công thức:

=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>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 criteria_range để công thức vẫn đúng khi sao chép sang các ô tính khác.

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 ô.

Xem thêm: TOP 10 NGÀNH NGHỀ HOT NHẤT TẠI VIỆT NAM HIỆN NAY

Xem Thêm : UltraViewer 6.4.7 Phần mềm điều khiển máy tính, hỗ trợ từ xa

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.

hàm đếm số lượng COUNTIF và COUNIFS

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,”<=6/7/2014″)

=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)

hàm đếm số lượng COUNTIF và COUNIFS

Ví dụ 2. Đếm ngày với nhiều điều kiện

Tương tự, bạn có thể sử dụng công thức COUNTIFS để đếm số ngày trong các cột khác nhau đáp ứng từ hai điều kiện trở lên. Ví dụ, công thức dưới đây sẽ tìm ra có bao nhiêu sản phẩm được mua sau ngày 20 tháng 5 và giao hàng sau ngày 01 tháng 6:

=COUNTIFS(C2:C9,”>5/1/2014″,D2:D9,”6/7/2014″)

hàm đếm số lượng COUNTIF và COUNIFS

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(),D2:D9,”>”&TODAY())

→ Xem thêm:

Khóa học Thực hành Excel trong Kiểm toán

Khóa học Ứng dụng VBA trong Excel

Khóa học Ứng dụng Dashbaord Reporting trong Excel

Tham khảo: Nhân viên Marketing (Marketing Executive) là gì?

Tags

  • Excel tips

Nguồn: https://dongnaiart.edu.vn
Danh mục: Cẩm Nang Việc

Lời kết: Trên đây là bài viết Cách dùng hàm đếm số lượng COUNTIF và COUNIFS nhiều điều kiện. Hy vọng với bài viết này bạn có thể giúp ích cho bạn trong cuộc sống, hãy cùng đọc và theo dõi những bài viết hay của chúng tôi hàng ngày trên website: Dongnaiart.edu.vn

Related Posts

Cách viết thư cảm ơn sau khi phỏng vấn chuyên nghiệp

Cách viết thư cảm ơn sau khi phỏng vấn chuyên nghiệp

Sau buổi phỏng vấn trực tiếp với nhà tuyển dụng, bạn đừng quên gửi thư cảm ơn nhé vì đơn thuần chỉ là trả lời email phỏng…

Cách khắc phục lỗi save trong word 2010 mà bạn nên biết

Cách khắc phục lỗi save trong word 2010 mà bạn nên biết

https://www.youtube.com/watch?v=lASide7oOQkCó thể bạn quan tâm Các Mẫu Báo cáo thành tích mới nhất năm 2021 cách nén file pdf để gửi mail cách vẽ biểu đồ cột…

Hướng dẫn cách ghi nhật ký chung chi tiết

Hướng dẫn cách ghi nhật ký chung chi tiết

Các số liệu kế toán được theo dõi tại các sổ kế toán. Ghi sổ kế toán theo hình thức nhật ký chung được nhiều doanh nghiệp…

20 Mẫu đoạn văn tiếng Anh về công việc tình nguyện hay nhất năm 2022

20 Mẫu đoạn văn tiếng Anh về công việc tình nguyện hay nhất năm 2022

https://www.youtube.com/watch?v=Dh_AhY48iGECó thể bạn quan tâm Tra cứu lương nhân viên viettel du lịch cộng đồng là gì mẫu cv tiếng anh cho sinh viên Kinh Doanh Vàng…

Mẫu lời tri ân khách hàng hay, sâu sắc và ý nghĩa nhất 2022

Mẫu lời tri ân khách hàng hay, sâu sắc và ý nghĩa nhất 2022

https://www.youtube.com/watch?v=zvkH5Z0VN6MCó thể bạn quan tâm lập trình php bằng phần mềm nào Phần mềm là gì và cách phân loại phần mềm cơ bản Bà Thái Hương:…

6 thủ thuật excel cho dân văn phòng hữu ích, dễ thực hiện

Excel là phần mềm cực kỳ hữu ích nhất là trong công việc. Để thao tác với excel được nhanh, tiết kiệm thời gian, nâng cao năng…