Lệnh đếm có nhiều điều kiện trong Google Sheet COUNTIFS: Công thức và cách thức dùng

Lệnh đếm có nhiều điều kiện trong Google Sheet COUNTIFS: Công thức và cách thức dùng

Blog Article

Trong tất cả các lệnh Excel, hàm đếm có điều kiện COUNTIFS và COUNTIF là hai lệnh tính thông dụng nhất. Sự khác biệt tiêu biểu 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 1 phạm vi, trong khi COUNTIFS có thể đánh giá những điều kiện khác nhau trong cùng hoặc trong các phạm vi khác nhau. Để giúp bạn đọc có thể hiểu rõ hơn về hàm đếm có nhiều điều kiện trong Excel, mời bạn tham khảo nội dung bài viết sau đây để có câu trả lời nhé.

1. Công thức và cách sử dụng hàm đếm có nhiều điều kiện trong Excel COUNTIFS

Như đã đề cập, lệnh COUNTIFS trong Excel được sử dụng để đếm số lần xuấthiện giá trị trong Google Sheet trên đa dạng phạm vi dựa trên 1 hoặc 1 số điều kiện.

Lệnh COUNTIFS có sẵn trong Excel 2016, 2013, Google Sheet 2010 và GoogleSheet 2007, vì vậy bạn có thể dùng những ví dụ dưới đây trong bất kỳ phiên bản Google Sheet nào.

Công thức countif: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Trong đó:

- criteria_range1: mả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 mảng điều kiện thứ nhất) gồm có những dạng số, ô tham chiếu, chuỗi văn bản, lệnh GoogleSheet hoặc mảng Google Sheet 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 mả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.

- Tương tự như vậy với những điều kiện khác cũng sẽ áp dụng như vậy.



Công thức hàm đếm có đa dạng điều kiện trong Excel

Thực tế, bạn không cần phải nhớ cú pháp của hàm đếm có điều kiện COUNTIFS. Microsoft Excel sẽ hiển thị các đối số của lệnh ngay khi bạn bắt đầu nhập; đối số bạn đang nhập vào lúc này được tô đậm. Bạn chỉ cần nhớ bạn muốn dùng hàm COUNTIFS trong Excel là được.

LƯU Ý: - Bạn có thể sử dụng lệnh COUNTIFS trong GoogleSheet để đếm những ô trong một phạm vi với 1 điều kiện duy nhất cũng như trong đa dạng khuôn khổ với nhiều điều kiện.

Nếu sau này, chỉ những ô đáp ứng tất cả các điều kiện được chỉ định mới được tính.

- Criteria1 bạn có thể biểu diễn chúng giống như những ví dụ: 10, "<=32", A4, "gord"...

- Mỗi phạm vi bổ sung phải có cùng số hàng và cột như phạm vi đầu tiên.Cả khuôn khổ tiếp giáp và không liền kề đều được cho phép.

- Nếu điều kiện là một tham chiếu đến một ô trống , hàm COUNTIFS coi nó là giá trị 0 (0).

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



dùng lệnh COUNTIFS trong Excel để đếm có điều kiện

2. Cách sử dụng lệnh COUNTIFS trong Google Sheet

Dưới đây bạn sẽ tìm thấy một số ví dụ về công thức minh họa cách thức sử dụng các lệnh COUNTIFS trong Excel để đánh giá nhiều điều kiện.

Thông qua các ví dụ được minh họa dưới đây bạn sẽ hình dung ra được cách thức dùng các lệnh này trong các trường hợp cụ thể tương ứng để có thể áp dụng lệnh COUNTIFS trong GoogleSheet để đánh giá đa dạng điều kiện khác nhau

2.1. Cách thức đếm ô tính với nhiều điều kiện (AND logic)

Công thức một. Công thức COUNTIFS với đa dạng điều kiện khác nhau

Ví dụ bạn có 1 danh sách sản phẩm như hiển thị trong ảnh chụp màn hình bên dưới. Bạn muốn có được 1 số lượng những mặt hàng trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị là cột C bằng 0).

Khi này bạn thực hiện bằng cách sử dụng công thức đếm có điều kiện: =COUNTIFS(B2:B7,">0", C2:C7,"=0") Và số lượng là 2 ("Anh đào " và " Chanh "):



Công thức lệnh Countifs

Công thức 2. Công thức COUNTIFS có cùng tiêu chí

Khi bạn muốn đếm những mục có điều kiện giống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện .

Ví dụ: đây là công thức đúng để đếm những mục có 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ỉ " Nho " có giá trị "0" trong cả hai cột

Công thức hàm COUNTIFS có cùng tiêu chí

2.2. Cách đếm ô có đa dạng điều kiện khác nhau (logic OR)

Như bạn đã thấy trong những ví dụ trên, việc đếm những ô đáp ứng tất cả những điều kiện đã chỉ định là thuận lợi vì hàm COUNTIF đa dạng điều kiện được thiết kế để hoạt động theo cách này.

Nhưng nếu bạn muốn đếm những ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao?

Nhìn chung, có hai cách để thực hiện việc này - bằng phương pháp thêm 1 số công thức COUNTIFS hoặc dùng công thức SUM COUNTIFS. Công thức một. Thêm hai hoặc đa dạng công thức trong COUNTIFS

Ví dụ: để có được số lượng đơn đặt hàng " Đã hủy " và " Đang chờ xử lý" cho " Táo ", hãy dùng công thức sau:

=COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đã hủy") + COUNTIFS($A$2:$A$11, "Táo", $C$2:$C$11,"Đang chờ xử lý")



Công thức đếm ô tính có nhiều điều kiện

Công thức 2. SUMIFS với hằng số mảng

Trong những tình huống khi bạn phải đánh giá rất đa dạng điều kiện thì phương pháp trên không phải là cách thức tốt nhất vì công thức của bạn sẽ quá dài.

Để thực hiện những phép tính tương tự trong 1 công thức nhỏ gọn hơn, hãy liệt kê tất cả các điều kiện của bạn trong 1 hằng số vùng và cung cấp mảng đó cho đối số điều kiện của hàm đếm có điều kiện COUNTIFS.

Ta sẽ sử dụng công thức sau: =SUM(COUNTIFS(range,"criteria1","criteria2","criteria3",…))

Trong bảng dưới đây, để đếm ‘Táo” có trạng thái " Đã hủy " hoặc " Đang chờ xử lý " hoặc " Nhập kho ", công thức sẽ như sau: =SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,"Đã hủy","Đang chờ xử lý","Nhập kho"))



SUMIFS với hằng số mảng

2.3. Phương pháp đếm số trong khoảng cụ thể

Ngoài các phương pháp dùng hàm đếm có đa dạng điều kiện trong GoogleSheet như bên trên đã chia sẻ, bạn cũng có thể dùng hàm COUNTIFS để đếm số trong khoảng thời gian cụ thể. Đối với vấn đề này thì sẽ có 2 công thức cơ bản nhất, cụ thể đó là:

2.3.1. Công thức 1: hàm COUNTIFS đếm các ô trong khoảng số

Công thức COUNTIFS 2 điều kiện sử dụng để đếm những ô giữa hai số Để tìm hiểu có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ C2 đến C10, hãy dùng công thức này: = COUNTIFS (C2: C10, "> 5", C2: C10, "<10")



Hàm COUNTIFS đếm các ô trong khoảng số.

Để đếm các số 5 và 10 trong số đếm, hãy dùng công thức "lớn hơn hoặc bằng" và "nhỏ hơn hoặc bằng": = COUNTIFS (B2: B10, "> = 5", B2: B10, "<= 10").

2.3.2. Công thức COUNTIF để đếm số giữa X và Y

Bạn có thể ứng dụng 2 công thức sau: =COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)

– Đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 trong mả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 mảng dữ liệu C2:C10, bao gồm cả 5 và 10.

Trong đó:

- Công thức đầu tiên dùng để đế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ứ 2 loại bỏ những cố lớn hơn giá trị giới hạn trên (Ví dụ là 10)

Do vậy kết quả nhận được sẽ nằm trong khoảng giá trị nhận được trên.

>>> Xem thêm: Phương pháp đếm số lượng trong GoogleSheet đơn giản và chuẩn nhất

2.4. Cách thức dùng COUNTIFS với những ký tự đại diện

Trong các câu thức hàm đếm có nhiều điều kiện trong Google Sheet, bạn có thể dùng những ký tự đại diện như sau:

- ? : được dùng để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.

- * " được dùng để đếm ô chứa một từ cụ thể hoặc một k1 tự trong ô.

Ví dụ 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ể 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 COUNTIFS với những ký tự đại diện

3. Cách thức dùng lệnh COUNTIFS theo điều kiện ngày tháng

hàm countifs có thể giúp bạn làm được rất nhiều việc, tiêu biểu trong đó là việc dùng hàm countifs theo điều kiện ngày tháng để đếm ngày. Có 3 phương pháp để bạn có thể sử dụng hàm countifs đếm thời gian, tiêu biểu đó là:

3.1. Đếm ngày trong 1 khoảng thời gian cụ thể

Với ví dụ này, bạn có thể sử dụng 2 hàm COUNTIFS nhiều điều kiện và COUNTIF.

Ví dụ, công thức sau được sử dụng để đếm số ngày trong ô C2-C10 với thời gian từ 1/6/2014 - 7/7/2014:

=COUNTIFS(C2:C9,”=6/1/2014″,C2:C9,”<=6/7/2014″) công thức countifs =COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)



Công thức đếm ngày trong một khoảng thời gian cụ thể

3.2. Đếm ngày với đa dạng điều kiện

Để đếm ngày trong các cột khác nhau từ 2 điều kiện trở lên, bạn có thể dùng lệnh COUNTIFS.

Ví dụ: Tìm ra xem có bao nhiêu sản phẩm được mua sau 20/5 và giao sau 1/6, Công thức Vận dụng như sau:

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



Công thức đếm ngày với đa dạng điều kiện

3.3. Đếm ngày với đa dạng điều kiện dựa trên ngày hiện tại

Với trường hợp này, bạn có thể dùng hàm COUNTIFS kết hợp với hàm TODAY.

Ví dụ: Dùng hàm COUNTIFS để xác định 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 đếm ngày dựa trên ngày hiện tại

4. Hàm đếm trong GoogleSheet có điều kiện COUNTIFS phối hợp SUM trong GoogleSheet

Về cơ bản lệnh COUNTIFS cũng không khác gì hàm COUNTIF là mấy, chẳng qua phương pháp dùng của nó có phần phức tạp hơn.

Nếu bạn muốn sử dụng hàm COUNTIFS và SUM để đếm có điều kiện trong Excel thì bạn thực hiện như sau:

Ví dụ: Lập bảng trong GoogleSheet chứa thông tin về màu sắc táo và kích thước của chúng. Mục tiêu cuối cùng của ví dụ này đó là đếm số lượng táo đỏ cỡ lớn.



Lập bảng trong GoogleSheet

Tiếp theo bạn chọn ô muốn hiển thị kết quả của công thức

=> Nhập công thức =SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big")) => Enter.

Khi này, Google Sheet đã đếm cho bạn số lượng táo cỡ lớn hoặc táo có màu đỏ.



Kết quả đếm cuối cùng

5. Cách sửa những lỗi thường gặp khi dùng lệnh đếm có điều kiện trong Google Sheet

Trong quá trình dùng lệnh đếm có điều kiện lớn hơn và nhỏ hơn người dùng rất hay gặp lỗi. Sau đây là 1 số lỗi thường gặp cùng phương pháp khắc phục cho bạn tham khảo, lưu lại ngay nhé.

5.1. COUNTIFS không hoạt động khi đếm các giá trị text

Khi đếm những giá trị text, để hoạt động chúng phải được điền vào trong dấu ngoặc kép. Nếu không được chèn, hàm COUNTIFS sẽ không thể đếm được, khi không đếm được chuỗi văn bản sẽ trả về giá trị 0. Ví dụ dưới đây text không đặt trong dấu ngoặc kép nên kết quả trả về bằng 0



Kết quả trả về bằng 0 do sai công thức

Để khắc phục lại này bạn chỉ cần nhập lại công thức có dấu ngoặc ở text là được. Công thức: =COUNTIFS(E5:E12, "Car")



Công thức đúng khi đếm giá trị text.

5.2. COUNTIFS không hoạt động vì sai tham chiếu phạm vi

Khi sử dụng hàm đếm có đa dạng điều kiện trong GoogleSheet bạn rất hay gặp lỗi lệnh COUNTIFS không hoạt động vì sai tham chiếu khuôn khổ.

Vì vậy khi sử dụng nhiều hơn 1 tiêu chí trong hàm COUNTIFS, khuôn khổ của ô tiêu chí khác phải có cùng số lượng. Nếu không hàm COUNTIFS sẽ lỗi.

Ví dụ: Bạn muốn đếm doanh số bán ô tô ở Austin.

Công thức bạn nhập vào bảng đó là =COUNTIFS(E5:E12,"Car",D5:D11,"Austin").

Quan sát kỹ công thức, bạn sẽ thấy khuôn khổ tiêu chí đầu tiên ở đây là E5:E12 nhưng khuôn khổ ở tiêu chí thứ hai là D5:D11. Khi số ô trong khuôn khổ cho tiêu chí không giống nhau thì kết quả sẽ trả về lỗi #VALUE!.



Công thức nhập vào bảng tiêu chí không giống nhau

Cách thức khắc phục đó là bạn nhập lại công thức thành

=COUNTIFS(E5:E12,"Car",D5:D12,"Austin").

Như vậy mới trả về kết quả đúng được.

5.3. COUNTIFS không hoạt động vì lỗi trong công thức

Trong quá trình làm cho việc với COUNTIFS nếu không chèn công thức chính thức thì chắc chắn sẽ bị lỗi lệnh không hoạt động.

Nếu bạn sử dụng toán tử học, ví dụ như: dấu lớn hơn (>), nhỏ hơn (<), bằng (=), không bằng (<>), thì cả toán tử và tiêu chí số đều phải được nhập vào bên trong cùng một phương trình.

Ví dụ: Bạn đang muốn tìm doanh số bán hàng lớn hơn 100.000USD, bạn nhập công thức là:

=COUNTIFS(F5:F12,">" 100000).

Khi này bạn chỉ mới chèn toán tử bên trong phương trình chứ không có tiêu chí số nên kết quả lỗi hiển thị " “There’s a problem with this formula”. phương pháp khắc phục đó là bạn nhập công thức:

=COUNTIFS(F5:F12,">100000") => Enter.

Khi đã nhập cả hai toán tử và tiêu chí bên trong dấu ngoặc thì bạn sẽ nhận được kết quả đúng như mong muốn.



Kết quả trả về khi nhập đúng công thức

6. Những điều cần lưu ý khi sử dụng lệnh COUNTIFS

Dưới đây là 1 số điều cần lưu ý khi sử dụng lệnh COUNTIFS, bạn hãy ghi nhớ nhé:

- Các mảng chọn bổ sung thiết yếu cùng số hàng và cột với mảng criteria_range1. Nếu không, lệnh COUNTIFS sẽ trả về kết quả sai.

- Điều kiện mảng chọn tham chiếu đến ô trống thì lệnh COUNTIFS tự động coi giá trị là 0.

- Có thể dùng những ký tự đại diện— dấu hỏi (?) để tìm kiếm các giá trị không xác định.

Ví dụ, công thức =COUNTIFS(A2:A10,"A") sẽ đếm tất cả các ô trong phạm vi A2:A10 có chứa chữ "A" ở bất kỳ vị trí nào.

- Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường.

Ví dụ, công thức =COUNTIFS(A2:A10,"a") sẽ đếm tất cả những ô trong khuôn khổ A2:A10 có chứa chữ "a" hoặc "A".

Trên đây là toàn bộ thông tin về lệnh đếm có đa dạng điều kiện COUNTIFS trong GoogleSheet mà itchiasehay đã tổng hợp được. hàm đếm có điều kiện là cách thức thông dụng nhất để bạn thực hiện thao tác đếm trong GoogleSheet đơn giản, từ đó tiện dụng thống kê và phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc

Report this page