Cách dùng công thức đếm có điều kiện COUNTIFS và COUNTIF

Việc đếm các dữ liệu trong bài không phải là dễ dàng, đặc biệt là với những file Excel lượng thông tin lớn. Vậy nên trong bài viết này sẽ giới thiệu đến các bạn công thức đếm có điều kiện COUNTIFS và COUNTIF, kèm theo đó là những ví dụ cụ thể. Bây giờ hãy cùng Mindovermetal tìm hiểu hai vấn đề này.

Cấu trúc Hàm COUNTIF và ví dụ liên quan

Hàm COUNTIF là gì?

Hàm đếm có điều kiện trong Excel, thuộc nhóm COUNT. Mục đích giúp chúng ta trong việc thống kê, báo cáo, đếm các ô trùng lặp hay đếm dữ liệu.

Cú pháp hàm COUNTIF: =COUNTIF(range;criteria)

Trong đó:

• Range: Vùng đếm dữ liệu, có thể chứa số, mảng hoặc tham chiếu có chứa số. Các giá trị trống sẽ được bỏ qua.
• Criteria: Điều kiện để đếm các giá trị trong range, có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.

Một số điều kiện và lưu ý để sử dụng hàm này:

• Hàm COUNTIF trả về kết quả sau khi sử dụng điều kiện với các chuỗi ký tự hơn 255 ký tự.
• Đối số criteria cần để trong dấu ngoặc kép. Không phân biệt chữ thường hay chữ hoa.

Sử dụng hàm COUNTIF để đếm số lượng hàng hóa

Ví dụ 1: Đếm số lượng tên hàng Dasani trong bảng

Chúng ta có công thức thực hiện là =COUNTIF(B2:B11,”Dasani”) rồi nhấn Enter để thực hiện hàm.

Ta có được kết quả là 1 như hình dưới

Ví dụ 2: Tìm số lượng hàng không phải Diet Coke trong bảng

Chúng ta sử dụng điều kiện tên hàng không phải là Diet Coke là “<>diet coke” rồi nhập công thức =COUNTIF(B2:B11,”<>diet coke”). Rồi nhấn Enter để thực hiện.

Kết quả sẽ ra 9 mặt hàng không có tên chanh trong bảng dữ liệu.

Ví dụ 3: Tìm số mặt hàng có số lượng bán >= 20 lon

Điều kiện sử dụng với nội dung là “>=20” tại cột số lượng bán với công thức hàm là =COUNTIF(C1:C11,”>=20″) và nhấn Enter.

Kết quả sẽ ra 6 mặt hàng với số lượng bán >= 20 lon.

Trên đây giải thích thế nào là hàm COUNTIF và các cách sử dụng cơ bản cùng với những ví dụ thực tế trong công thức đếm có điều kiện. Nên lưu ý với dấu phân cách , hoặc ; sẽ tùy vào từng máy, nếu báo lỗi ở phần dấu bạn cần kiểm tra lại dấu phân cách.

Cấu trúc Hàm COUNTIFS và ví dụ liên quan

Hàm COUNTIFS là gì?

Giống với hàm COUNTIFS hàm đếm có điều kiện trong Excel, thuộc nhóm COUNT. Mục đích giúp chúng ta trong việc thống kê, báo cáo, đếm các ô trùng lặp hay đếm dữ liệu.

Tuy nhiên,sự khác biệt duy nhất giữa hai hàm này là COUNTIF được thiết kế để đếm các ô có một điều kiện trong một phạm vi, trong khi COUNTIFS có thể đánh giá các điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau.

Hàm COUNTIFS có cú pháp là: =COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)

Trong đó:

• criteria_range1: 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 (thuộc trong vùng điều kiện thứ nhất) gồm có các dạng số, ô tham chiếu, chuỗi văn bản, hàm Excel hoặc vùng Excel khác.
• criteria_range2: Vùng chứa điều kiện thứ hai( yêu cầu vùng criteria_range2 phải có độ lớn bằng nhau)
• criteria2,…: Điều kiện thứ hai (thuộc trong vùng điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi/điều kiện trong công thức của mình.

Cú pháp chúng ta có thể không cần nhớ vì khi nhập Microsoft Excel sẽ hiển thị các đối số của hàm ngay.

Lưu ý: Hàm COUNTIFS không dùng được ở các phiên bản Microsoft Office thấp hơn 2007

Ví dụ về sử dụng hàm COUNTIFS đếm số lượng lon và tồn kho

Đếm số hàng là dasani có số lượng 20 lon trở lên hoặc số tồn kho lớn hơn hoặc bằng 100.

Yêu cầu là: “Đếm số hàng là dasani có số lượng 20 lon trở lên hoặc số tồn kho lớn hơn hoặc bằng 200 “. Đây là yêu cầu rất rõ ràng về nghĩa tiếng Việt. Nhưng để Excel hiểu được yêu cầu này để tính toán được thì chúng ta cần phải phân tích cụ thể hơn:

• Đối tượng có liên quan: Cột Tên hàng, cột số lượng, cột số tồn kho
• Điều kiện cụ thể: Tên hàng = Dasani, số lượng >= 20, số tồn kho >=100
• Mối liên quan giữa các điều kiện là gì?

Nếu không có mối liên quan thì sẽ đếm riêng lẻ từng điều kiện rồi cộng lại. Nhưng ở đây các điều kiện có mối quan hệ với nhau

Tên hàng là Dasani có số lon 20 trở lên: Trong này bao hàm điều kiện “VÀ” (AND). Hoặc có số tồn kho lớn hơn hoặc bằng 100: Trong này bao hàm điều kiện “HOẶC” (OR)

Cụ thể như sau:

• Yc1: Tên hàng là Dasani và có số lượng lon từ 20 trở lên
• Yc2: Tên hàng là Dasani và có số tồn kho hơn hoặc bằng 100
• Yc1 hoặc yc2 đều đúng

Xây dựng công thức cho hàm đếm có điều kiện COUNTIFS

Xét yêu cầu 1: Gồm 2 điều kiện

Các đối tượng trong hàm COUNTIFS bao gồm:

• Criteria_range1: Vùng chứa điều kiện thứ 1, cụ thể là cột Tên hàng B2:B11
• Criteria1: Điều kiện thứ 1 (nằm trong vùng điều kiện thứ 1), cụ thể là Tên hàng là Dasani
• Criteria_range2: Vùng chứa điều kiện thứ 2, cụ thể là cột Số lượng C2:C11 (yêu cầu là các vùng criteria_range phải có độ lớn bằng nhau)
• Criteria2: Điều kiện thứ 2 (nằm trong vùng điều kiện thứ 2), cụ thể là Số lượng >=20

Cách viết hàm COUNTIFS như sau: =COUNTIFS(B2:B11,”Dasani”,C2:C11,”>=”&20)

Tương tự như vậy với yêu cầu 2 chúng ta có công thức: =COUNTIFS(B2:B11,”Dasani”,C2:C11,”>=”&100)

Vì 2 điều kiện này là dạng Hoặc : Chỉ cần đúng 1 trong 2 trường hợp thì đều được tính nên công thức tổng quát
=COUNTIFS(B2:B11,”Dasani”,C2:C11,”>=”&20)+COUNTIFS(B2:B11,”Dasani”,D2:D11,”>=”&100)

Hai công thức đếm có điều kiện COUNTIF VÀ COUNTIFS thường dễ bị nhầm lẫn vì mục đích sử dụng khá giống nhau và cách viết từ tạo nên hàm. Nhưng bạn nên phân biệt rõ ràng hai hàm số để có thể áp dụng hợp lý tránh sai sót. Qua bài viết này, chắc chắn bạn sẽ hiểu rõ hơn về công thức đếm có điều kiện. Theo dõi Mindovermetal để có thêm nhiều công thức Excel hay ho nhé!