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, “<=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.
COUNTIFS – Những điều cần lưu ý:
- 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.
- 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).
- Có thể đếm những vùng dữ liệu liền
kề và không liền kề.
- 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 (0).
- 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 sử dụng hàm COUNTIFS và COUNTIF trong excel với
nhiều tiêu chí:
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”):
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).
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,”<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″)
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 và 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 ô.
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,”<=6/7/2014″)
=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)
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″)
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())
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()-7,D2:D9,”>”&TODAY())
Đăng nhận xét