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

Trong tất cả 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 vì chúng trông vô cùng giống nhau và cả hai hầu hết cùng gồm mục đíchdùng nhằm đếm các ô dựa trên những điều kiệnđã được chỉ định.

Bạn đang xem: Công thức đếm có điều kiện

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

Nhằm khiến cho bạn đọc rành mạch một cách chính xác và núm được cách áp dụng hai hàm đếm có điều kiện COUNTIFSđúng vào Excel, trong nội dung bài viết này h3qvn.com sẽ ra mắt và hướng dẫn bạn đọc cách áp dụng và tuyển lựa công thức công dụng nhất mang đến từng nhiệm vụ từng việc cụ thể, đặc biệt là đối 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 gồm sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, vì chưng vậy bạn cũng có thể sử dụng những ví dụ tiếp sau đây trong ngẫu nhiên phiên bạn dạng học Excel nào.

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

Trong đó:

- criteria_range1: Vùng chứa đk thứ nhất, đó 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ó những 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 vật dụng 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ệntrong bí quyết của mình.

- giống như như vậy với những điều kiện khác cũng trở 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 vẫn hiển thị những đối số của hàm ngay trong lúc bạn bắt đầu nhập; đối số nhiều người đang nhập vào lúc này được tô đậm.

Bạn chỉ việc 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 vào Excel để đếm những ô vào một phạm vi cùng với một điều kiện duy nhất cũng giống như trong nhiều phạm vi với tương đối nhiều điều kiện. Ví như sau này, chỉ đều ô đáp ứng nhu cầu tất cả những điều kiện được chỉ định new được tính.

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

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

- bạn có thể sử dụng các ký từ đại diệntrong đk - vệt hoa thị (*) cùng dấu hỏi (?).

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

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

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

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

Công thức 1. Bí quyết COUNTIFS với tương đối nhiều điều kiện không giống nhau

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

Nhiệm vụ hoàn toàn 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")

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

*

Hình minh họa

Công thức 2. Phương pháp COUNTIFS bao gồm cùng tiêu chí

Khi bạn có nhu cầu đếm những mục có điều kiệngiống hệt nhau, các 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 nhằm đếm những mục gồm 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 do chỉ " Nho " có mức giá trị "0" trong cả nhị cột

*

Hình minh họa

2.2. Biện pháp đếm ô có nhiều điều kiện không giống nhau (logic OR)

Như chúng ta đã thấy trong những ví dụ trên, câu hỏi đếm những ô đáp ứng tất cả các điều kiện đã hướng dẫn và chỉ định là dễ ợt vì hàm đếm tất cả điều kiệnCOUNTIFS được thiết kế với để vận động theo giải pháp này.

Xem thêm: 8 Mâm Quả Đám Cưới Miền Tây Gồm Những Gì? Trọn Gói 8 Mâm Quả Cưới Hỏi

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

Công thức 1. Thêm hai hoặc nhiều phương pháp trong COUNTIFS

Ví dụ: để sở hữu được con số đơn mua hàng " Đã bỏ " và " Đang ngóng xử lý" mang đến " táo apple ", hãy thực hiện 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 với hằng số mảng

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

Để thực hiện các phép tính tương tự trong một công thức nhỏ dại gọn hơn, hãy liệt kê tất cả các điều kiệncủa các bạn trong một hằng số mảng và hỗ trợ mảng đó đến đối số điều kiệncủa hàm đếm tất cả điều kiệnCOUNTIFS.

Ta sẽ dùng bí quyết sau:

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

Trong bảng bên dưới đây, nhằm đếm ‘Táo” có trạng thái " Đã bỏ " hoặc " Đang chờ cách xử lý " hoặc " Nhập kho ", phương pháp 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. Biện pháp đếm số trong khoảng cụ thể

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

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

Để tò mò có bao nhiêu số trường đoản cú 5 mang đến 10 (không bao hàm 5 và 10) được chứa trong các ô từ bỏ C2 mang đến C10, hãy thực hiện công thức này:

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

*

Hình minh họa

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

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

Công thức 2: công thức COUNTIF để đếm số thân X cùng Y

Bạn hoàn toàn có thể áp dụng 2 bí quyết sau:

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)– đếm có bao nhiêu số lớn hơn 5 nhỏ hơn 10 vào vùng dữ liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)– cách làm đếm tất cả bao nhiêu số trong khúc từ 5 cho 10 trực thuộc vùng dữ iệu C2:C10, bao hàm cả 5 cùng 10.

Trong đó:

- Công thức thứ nhất dùng nhằm đếm phần nhiều số to hơn giá trị số lượng giới hạn dưới (Ví dụ là 5)

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

Do vậy tác dụng nhận được đang nằm trong khoảng giá trị nhận được trên.

Cách áp dụng tham chiếu ô trong phương pháp COUNTIFS

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

Bạn vận 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 xào luộc sang những ô tính khác.

*

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

2.4. Cách áp dụng COUNTIFS với những ký từ đại diện

Trong các câu thức Excel, bạn có thể sử dụng những ký tự thay mặt đại diện như sau:

- ? : được sử dụng để đếm các ô bước đầu hoặc hoàn thành bằng một trong những ký tự tuyệt nhất định.

- * " Được thực hiện để đếm ô đựng một từ ví dụ 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 có nhu cầu biết có bao nhiêu dự án công trình đã ký kết kết, tức là cột B có chứa thương hiệu người. Hãy thêm một điều kiện thứ hai - over Date vào cột D.

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

Lưu ý, các bạn không thể áp dụng ký tự đại diện thay mặt trong tiêu chuẩn thứ 2 vì chưng cột D cất kiểu dữ liệu ngày tháng chứ chưa hẳn dạng text. Đó là nguyên nhân tại sao chúng ta sử dụng tiêu chuẩn “”&”” nhằm tìm ô đựng dữ liệu.

*

Hình minh họa

3.COUNTIFS và COUNTIF với nhiều tiêu chí ngày tháng

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

Với lấy ví dụ như này, bạn 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 vừa lòng này, bạn cũng có thể sử dụng hàm COUNTIF kết phù hợp với hàm TODAY.

Ví dụ: cần sử dụng hàm COUNTIF để xác định có bao nhiêu sản phẩm đã được sở hữu nhưng không giao hàng:

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

*

Hình minh họa

Trên đấy là hàm đếm có điều kiện và các cách phổ biến nhất để bạn tiến hành sử dụng các hàm đếm đótrong Excel, tự đó, dễ dãi thống kê và phân tích số liệu một giải pháp hiệu quả, nâng cao tối đa công suất công việc.Ngoài ra độc giả quan tâm rất có thể tham khảothêm các khoáhọc tin học văn phòngnhư học Word online, học Powerpoint cơ bảnsau trên h3qvn.com và sàng lọc cho mình khóa họchay phù hợp nhất nhé!Cảm ơn các bạn đã đọc bài bác và chúc bạnthành công!