Trong toàn bộ các hàm Excel, hàm đếm có điều kiện COUNTIFS với COUNTIF có lẽ thường dễ bị nhầm lẫn nhất bởi chúng trông vô cùng giống nhau cùng cả hai số đông cùng bao gồm mục đíchdùng để đếm những ô dựa trên những điều kiệnđã được chỉ định.

Bạn đang xem: Các hàm đếm có điều kiện trong excel

Sự khác hoàn toàn duy tuyệt nhất giữa nhì hàmnàylà COUNTIF được thiết kế để đếm các ô bao gồm một điều kiện trong một phạm vi, trong lúc COUNTIFS hoàn toàn có thể đánh giá những điều kiện khác biệt trong cùng hoặc trong số phạm vi không giống nhau.

Nhằm giúp bạn đọc minh bạch một cách đúng chuẩn và nuốm được cách áp dụng hai hàm đếm có điều kiện COUNTIFSđúng trong Excel, trong bài viết này hanoitranserco.com.vn sẽ giới thiệu và hướng dẫn bạn đọc cách thực hiện và tuyển lựa công thức tác dụng nhất đến từng trọng trách từng việc cụ thể, nhất là đối cùng với hàmCOUNTIFS.

1. Hàm COUNTIFS của Excel - cú pháp và bí quyết sử dụng

Như đã đề cập, hàm COUNTIFS của Excel được dùng làm đếm các ô trên các phạm vi dựa trên một hoặc một số trong những điều kiện. Hàm COUNTIFS có sẵn vào Excel 2016, 2013, Excel 2010 và Excel 2007, vị vậy bạn cũng có thể sử dụng các ví dụ tiếp sau đây trong bất kỳ phiên bảnExcelnào.

Cú pháp: =COUNTIFS(criteria_range1, criteria1, ,…)

Trong đó:

- criteria_range1: Vùng chứa đk thứ nhất, đấy là phần cần phải có

- criteria1: Điều kiện thứ nhất (thuộc trong vùng đk thứ nhất) tất cả 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 đk thứ hai( yêu cầu vùng criteria_range2 phải gồm độ lớn bởi nhau)

-criteria2, ... Điều kiện trang bị hai (thuộc vào vùng điều kiện thứ nhất). Bạn cũng có thể chỉ định về tối đa 127 cặp phạm vi / điều kiệntrong công thức của mình.

- tương tự như vậy với các điều kiện khác cũng biến thành áp dụng như vậy.

*

Hình minh họa

Thực tế, bạn không nhất thiết phải nhớ cú pháp của hàm đếm bao gồm điều kiệnCOUNTIFS. Microsoft Excel đã hiển thị những đối số của hàm ngay trong lúc bạn bước đầu nhập; đối số ai đang nhập vào bây giờ được đánh đậm.

Bạn chỉ cần nhớ bạn muốn sử dụng hàm COUNTIFS trongExcel là được.

LƯU Ý:

- bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô vào một phạm vi cùng với một đk duy nhất cũng như trong nhiều phạm vi với khá nhiều điều kiện. Nếu sau này, chỉ hầu như ô đáp ứng tất cả các điều khiếu nại được chỉ định new được tính.

- Criteria1bạn có thể biểu diễn chúng y như các ví dụ: 10, "- mỗi phạm vi bổ sung phải gồm cùng số hàng cùng cột như phạm vi đầu tiên.Cả phạm vi tiếp tiếp giáp và không liền kề phần nhiều được cho phép.

- nếu điều kiệnlà một tham chiếu cho một ô trống , hàm COUNTIFS coi nó là quý hiếm 0 (0).

- chúng ta cũng có thể sử dụng các ký tự đại diệntrong điều kiện - vết hoa thị (*) với dấu hỏi (?).

2. Cách thực hiện hàm đếm có đk COUNTIFS với khá nhiều điều kiện

Dưới đây bạn sẽ tìm thấy một trong những ví dụ về phương pháp minh họa giải pháp sử dụng những hàm COUNTIFS vào Excel để reviews nhiều điều kiện.

Thông qua hầu hết ví dụ được minh họa bên dưới đây bạn sẽ hình dung ra được cách sử dụng những hàm này trong số trường hợp rõ ràng tương ứng để hoàn toàn có thể áp dụng hàmCOUNTIFS trong Excel để đánh giá nhiều điều kiện khác nhau

2.1. Biện pháp đếm những ô có rất nhiều điều kiện

Công thức 1. Cách làm COUNTIFS với tương đối nhiều điều kiện khác nhau

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

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

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

Và số lượng là 2 ("Anh đào " với " Chanh "):

*

Hình minh họa

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

Khi bạn có nhu cầu đếm những mục bao gồm điều kiệngiống hệt nhau, các bạn vẫn cần cung ứng từng cặp điều kiện_range / điều kiện.

Ví dụ: đấy là công thức đúng nhằm đếm các mục tất cả 0 cả vào cột B với 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ưng chỉ " Nho " có mức giá trị "0" vào cả hai cột

*

Hình minh họa

2.2. Giải pháp đếm ô có khá nhiều điều kiện khác biệt (logic OR)

Như bạn đã thấy trong số ví dụ trên, câu hỏi đếm các ô đáp ứng nhu cầu tất cả các điều kiện đã hướng dẫn và chỉ định là dễ dãi vì hàm đếm có điều kiệnCOUNTIFS được thiết kế để chuyển động theo cách này.

Xem thêm: Chi Phí Cho Một Ca Ghép Thận Hết Bao Nhiêu Tiền ? Bạn Cần Biết

Nhưng nếu bạn muốn đếm những ô mà ít nhất một trong những điều khiếu nại được hướng đẫn là TRUE , có nghĩa là dựa trên logic OR thì sao? nhìn chung, gồm hai cách để thực hiện việc này - bằng phương pháp thêm một vài công thức COUNTIFS hoặc áp dụng công thức SUM COUNTIFS.

Công thức 1. Thêm nhị hoặc nhiều bí quyết trong COUNTIFS

Ví dụ: để sở hữu được con số đơn đặt đơn hàng " Đã hủy " và " Đang hóng xử lý" đến " hãng apple ", hãy áp 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 hóng xử lý")

*

Hình minh họa

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

Trong các tình huống khi bạn phải reviews rất nhiều điều kiện,cách trên không hẳn là cách tốt nhất có thể vì bí quyết của các bạn sẽ quá dài.

Để triển khai các phép tính tương tự trong một công thức nhỏ tuổi gọn hơn, hãy liệt kê toàn bộ các điều kiệncủa chúng ta trong một hằng số mảng và cung ứng mảng đó cho đối số điều kiệncủa hàm đếm bao gồm điều kiệnCOUNTIFS.

Ta sẽ dùng phương pháp sau:

=SUM(COUNTIFS(range,"criteria1","criteria2","criteria3",…))

Trong bảng dưới đây, nhằm đếm ‘Táo” có trạng thái " Đã bỏ " hoặc " Đang chờ cách xử trí " hoặc " Nhập kho ", bí quyết sẽ như sau:

=SUM(COUNTIFS($A$2:$A$11,"Táo",$C$2:$C$11,"Đã hủy","Đang ngóng xử lý","Nhập kho"))

*

Hình minh họa

2.3. Cách đếm số trong tầm cụ thể

Công thức 1:Hàm COUNTIFS đếm những ô trong tầm số

Công thứcCOUNTIFS nhằm đếm các ô thân hai số

Để khám phá có bao nhiêu số tự 5 mang lại 10 (không bao gồm 5 với 10) được chứa trong các ô từ bỏ C2 cho C10, hãy áp dụng công thức này:

= COUNTIFS (C2: C10, "> 5", C2: C10, "

*

Hình minh họa

Để đếm những số 5 và 10 trong các đếm, hãy thực hiện công thức "lớn hơn hoặc bằng" và "nhỏ rộng hoặc bằng":

= COUNTIFS (B2: B10, "> = 5", B2: B10, "

Công thức 2: công thức COUNTIF nhằm đếm số giữa X và Y

Bạn hoàn toàn có thể áp dụng 2 công thức sau:

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)– đếm gồm bao nhiêu số lớn hơn 5 bé dại hơn 10 vào vùng dữ liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)– bí quyết đếm gồm bao nhiêu số trong đoạn từ 5 cho 10 ở trong vùng dữ iệu C2:C10, bao hàm cả 5 và 10.

Trong đó:

- Công thức trước tiên dùng để đếm đều số lớn hơn giá trị số lượng giới hạn dưới (Ví dụ là 5)

- bí quyết thứ 2 loại bỏ những cố lớn hơn giá trị số lượng giới hạn trên (Ví dụ là 10)

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

Cách sử dụng tham chiếu ô trong bí quyết COUNTIFS

Ví dụ: mang đến bảng tài liệu sau, hãy tính các đơn hàng "Apples" với số chi phí >200$.

Bạn áp dụng công thức như sau:=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)

Trong đó:

-criteria_range1trong A2: A11

-criteria_range2trong B2: B11.

Lưu ý áp dụng giá trị tuyệt đối trongcriteriavàcriteria_rangeđể phương pháp vẫn đúng khi coppy sang những ô tính khác.

*

Hình hình ảnh minh họa

2.4. Cách thực hiện COUNTIFS với các ký từ đại diện

Trong các câu thức Excel, chúng ta có thể sử dụng các ký tự thay mặt như sau:

- ? : được áp dụng để đếm các ô bước đầu hoặc hoàn thành bằng một vài ký tự tốt nhất định.

- * " Được áp dụng để đếm ô đựng một từ ví dụ hoặc một k1 tự vào ô.

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 công trình đã ký kết, có nghĩa là cột B bao gồm chứa tên người. Hãy thêm một điều kiện thứ nhị - kết thúc Date vào cột D.

=COUNTIFS(B2:B10,”*”,D2:D10,””&””))

Lưu ý, các bạn không thể sử dụng ký tự đại diện trong tiêu chí thứ 2 do cột D chứa kiểu tài liệu ngày mon chứ chưa phải dạng text. Đó là lý do tại sao chúng ta sử dụng tiêu chí “”&”” nhằm tìm ô cất dữ liệu.

*

Hình minh họa

3.COUNTIFS cùng COUNTIF cùng với nhiều tiêu chuẩn ngày tháng

Ví dụ 1: Đếm số ngày trong một khoảng thời gian cụ thể

Với lấy ví dụ này, chúng ta cũng có thể sử dụng 2 hàm COUNTIFS với COUNTIF.

*

Hình minh họa

Ví dụ 3: Đếm ngày với khá nhiều điều kiện dựa trên ngày hiện tại tại

Với trường đúng theo này, bạn có thể sử dụng hàm COUNTIF kết hợp với hàm TODAY.

Ví dụ: dùng hàm COUNTIF để khẳng định có bao nhiêu sản phẩm đã được sở hữu nhưng chưa giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

*

Hình minh họa

Trên đấy là hàm đếm có đk và những cách phổ biến nhất để bạn triển khai sử dụng những hàm đếm đótrong Excel, từ đó dễ ợt thống kê và phân tích số liệu một phương pháp hiệu quả, cải thiện tối đa hiệu suất công việc.

Ngoài kiến thức về hàm đếm có đk các bạn có thể tham khảo các khóahọc Excel trực tuyến trên hanoitranserco.com.vn với cực kỳ nhiều ưu đãi bạn nhé