Trong vượt trình thao tác trên Excel, chúng ta phải làm cho quen với tương đối nhiều các hàm toán khác nhau. Bao hàm hàm các bạn ít khi thực hiện đến, mặc dù lại gồm có hàm bạn phải thực hiện thường xuyên, điển trong khi hàm COUNTIFS. Sẽ có không ít bạn còn khá lạ cùng với hàm này, tuy nhiên nó thiệt sự rất bổ ích để bạn có thể sử dụng trong các phép toán đếm chứa được nhiều điều kiện. Hãy thuộc Tech12h mày mò về hàm COUNTIFS.
Ở những bài trước, Tech12h đã lý giải cho chúng ta một số hàm thực hiện trong Excel như hàm SUM để tính tổng, hàm SUMIF nhằm tính tổng bao gồm điều kiện, hàm đk IF giỏi hàm tính quý hiếm trung bình AVERAGE…và một số hàm không giống nữa. Và hôm nay, Tech12 đã tiếp tục mang đến cho chúng ta thêm một hàm mới nữa nhé là hàm COUNTIFS – hàm đếm chứa đựng nhiều điều kiện.
Bạn đang xem: Hàm đếm có 2 điều kiện trong excel
Nội dung bài viết gồm 2 phần:
Giới thiệu về hàm COUNTIFSVí dụ minh họa hàm COUNTIFS1. Ra mắt về hàm COUNTIFS
Hàm COUNTIFS là hàm dùng để làm đếm số ô vừa lòng hay tốt nhiều đk khác nhau.
Thực hóa học hàm COUNTIFS là hàm nâng cấp của hàm COUNTIF. Theo đó, nó tất cả thẻ hạn chế và khắc phục được một số hạn chế cơ bạn dạng mà hàm COUNTIF không khắc phục được. Cụ thể là nó rất có thể thực hiện nay phép đếm đựng nhiều điều kiện khác biệt một cách mau lẹ thay do hàm COUNTIF chỉ đếm được phép toán chứa một điều kiện.
Cú pháp của hàm như sau:
=COUNTIFS(Criteria_range1,Criteria1,
Trong đó:
Criteria_range1: vùng đếm 1 (bắt buộc)Criteria1: Điều kiện đếm 1 (bắt buộc)Criteria_range2: Vùng đếm 2Criteria2: Điều kiện đếm 2…..Ví dụ: Ta tất cả bảng như sau:
Yêu cầu bạn đếm số nhân viên có ngày công là 23 ngày.
Ta gồm công thức như sau:
=COUNTIFS(D2:D6,”nhân viên”,E2:E6,23) công dụng nhận được là 2
Để làm rõ hơn, thế tiết hơn, các bạn hãy đến cùng với ví dụ dưới đây.
2. Lấy ví dụ minh họa hàm COUNTIFS
Ta gồm bảng dữ liệu như sau:
Yêu cầu:
Đếm số bạn gái có 23 ngày công hồi tháng này?Đếm số nam bao gồm ngày nghỉ ngơi 0 ngày?Đếm số bạn nữ có 23 ngày công với ngày nghỉ thấp hơn hoặc bởi 1 ngày?Để giải quyết và xử lý các phép toán trên, ta tiến hành triển khai như sau:
Thứ nhất, đếm số chúng ta nữa tất cả 23 ngày công hồi tháng này.
Đầu tiên, bạn phải xác định được các vùng đếm và điều kiện:
Vùng đếm 1: Cột nam nữ C2:C9Điều kiện 1: NữVùng đếm 2: Cột ngày công E2:E9Điều kiện 2: 23 ngày công
Từ phân tích như trên, thêm vào cú pháp ta được:
=COUNTIFS(C2:C9,"nữ",E2:E9,23) tác dụng thu được là 4
Thứ hai, đếm số nam bao gồm ngày nghỉ 0 ngày.
Cũng tương tự, ta xác định vùng đếm với điều kiện:
Vùng đếm 1: Cột nam nữ C2:C9Điều kiện 1: NamVùng đếm 2: Cột ngày nghỉ ngơi F2:F9Điều kiện 2: 0 ngày nghỉ
Từ kia ta tất cả công thức tính như sau:
=COUNTIFS(C2:C9,"nam",F2:F9,0) công dụng thu được là 0.
Cuối cùng, đếm số bạn nữ có 23 ngày công và tất cả ngày nghỉ ngơi thấp hơn hoặc bởi 1 ngày.
Khác với hai câu hỏi ở trên, ở thắc mắc này tất cả chứa bố điều kiện.
Vùng đếm 1: Cột nam nữ C2:C9Điều kiện 1: NữVùng đếm 2: Cột ngày công E2:E9Điều khiếu nại 2: 23 ngày
Vùng đếm 3: Cột ngày nghỉ F2:F9Điều kiện 3: 0 ngày
Chỉ bắt buộc lắp vào cú pháp ta được phương pháp tính như sau:
=COUNTIFS(C2:C9,"nữ",E2:E9,23,F2:F9,0) công dụng thu được là 2.
Như vậy, mình đã hướng dẫn cho chúng ta làm quen và sử dụng hàm COUNTIFS. Nó sẽ không thực sự khó nếu như bạn nắm bắt và phát âm được công thức tính của nó. Giả dụ vẫn chưa triển khai được, các chúng ta cũng có thể comment bên dưới để được phía dẫn ví dụ hơn. Ngoài ra, để tìm hiểu thêm chúng ta có thể tham khảo thêm bài viết các hàm cơ phiên bản trên Excel. Chắc hẳn chắn, nó đã cung cấp cho mình rất các điều xuất xắc về điều khoản này. Giả dụ cảm thấy nội dung bài viết bổ ích, chúng ta hãy lượt thích và Share để không ít người được nghe biết hơn nhé. Xin cảm ơn!
Trong toàn bộ các hàm Excel, hàm đếm có điều kiện COUNTIFS cùng COUNTIF có lẽ rằng thường dễ dẫn đến nhầm lẫn nhất bởi chúng trông vô cùng giống nhau và cả hai rất nhiều cùng có mục đíchdùng nhằm đếm các ô dựa trên các điều kiệnđã được chỉ định.
Sự khác biệt duy nhất giữa hai hàmnàylà COUNTIF có thiết kế để đếm các ô bao gồm một đk trong một phạm vi, trong lúc COUNTIFS có thể đánh giá các điều kiện không giống nhau trong thuộc hoặc trong các phạm vi khác nhau.
Nhằm giúp cho bạn đọc sáng tỏ một cách đúng mực và gắng được cách thực hiện hai hàm đếm số lượngcó điều kiện COUNTIFSđúng trong Excel, trong nội dung bài viết này emcanbaove.edu.vn sẽ trình làng và phía dẫn độc giả cách sử dụng và chọn lọc công thức công dụng nhất đến từng trọng trách từng việc cụ thể, đặc biệt là đối cùng với hàm
COUNTIFS.
1. Hàm COUNTIFS của Excel - cú pháp và cách sử dụng
Như đang đề cập, hàm COUNTIFtrong
Excel được dùng để làm đếm những ô trên nhiều phạm vi dựa vào một hoặc một trong những điều kiện. Hàm COUNTIFS tất cả sẵn trong Excel 2016, 2013, Excel 2010 và Excel 2007, do vậy chúng ta có thể sử dụng các ví dụ sau đây trong bất kỳ phiên bản
Excelnào.
Công thức countif: =COUNTIFS(criteria_range1, criteria1,
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) 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 điều kiện 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 hai (thuộc vào vùng điều kiện thứ nhất). Chúng ta cũng có thể chỉ định về tối đa 127 cặp phạm vi / điều kiệntrong bí quyết của mình.
- tựa như như vậy với những điều khiếu nại khác cũng trở nên á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ị các đối số của hàm ngay trong khi bạn bắt đầu nhập; đối số nhiều người đang nhập vào từ bây giờ được sơn đậm.
Bạn chỉ cần nhớ bạn có nhu cầu sử dụng hàm COUNTIFS trong
Excel là được.
LƯU Ý:
- chúng ta có thể sử dụng hàm COUNTIFS trong Excel để đếm những ô trong một phạm vi cùng với một đk duy nhất cũng như trong những phạm vi với nhiều điều kiện. Trường hợp sau này, chỉ phần lớn ô đáp ứng tất cả những điều kiện được chỉ định new được tính.
- Criteria1bạn rất 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 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à không liền kề đều được cho phép.
- ví như điều kiệnlà một tham chiếu cho một ô trống , hàm COUNTIFS coi nó là quý giá 0 (0).
- bạn có thể sử dụng những ký trường đoản cú đại diệntrong đk - dấu hoa thị (*) với dấu hỏi (?).
2. Cách áp dụng hàm đếm có đk COUNTIFS với nhiều điều kiện
Dưới đây bạn sẽ tìm thấy một trong những ví dụ về bí quyết minh họa biện pháp sử dụng các hàm COUNTIFS trong Excel để reviews nhiều điều kiện.
Thông qua phần nhiều ví dụ được minh họa bên dưới đây các bạn sẽ hình dung ra được cách áp dụng những hàm này trong các trường hợp rõ ràng tương ứng để có thể áp dụng hàm
COUNTIFS trong Excel để review nhiều điều kiện khác nhau
2.1. Giải pháp đếm những ô có tương đối nhiều điều kiện
Công thức 1. Cách làm COUNTIFS với khá nhiều điều kiện khác nhauVí dụbạn có một 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 một số trong những lượng các sản phẩm 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ằng 0).
Nhiệm vụ hoàn toàn có thể được thực hiện bằng phương pháp sử dụng công thức này:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
Và số lượng là 2 ("Anh đào " cùng " Chanh "):
Hình minh họa
Công thức 2. Bí quyết COUNTIFS bao gồm cùng tiêu chíKhi bạn muốn đế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 để đếm các mục bao gồm 0 cả trong cột B cùng cột C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
Công thức COUNTIFS này trả về 1 bởi vì chỉ " Nho " có mức giá trị "0" trong cả nhì cột
Hình minh họa
2.2. Cách đếm ô có khá nhiều điều kiện không giống nhau (logic OR)
Như các bạn đã thấy trong các ví dụ trên, việc đếm những ô đáp ứng tất cả những điều khiếu nại đã hướng đẫn là thuận lợi vì hàm COUNTIF nhiều điều kiệnđược thi công để chuyển động theo giải pháp này.
Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong số điều khiếu nại được chỉ định và hướng dẫn là TRUE , tức là dựa bên trên logic OR thì sao? nhìn chung, bao gồm hai cách để thực hiện bài toán này - bằng cách thêm một số 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 COUNTIFSVí dụ: để sở hữu được con số đơn đặt hàng " Đã hủy " cùng " Đang hóng xử lý" mang đến " táo bị cắn dở ", 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 đợi xử lý")
Hình minh họa
Công thức 2. SUMIFS với hằng số mảngTrong các tình huống khi chúng ta phải reviews rất các đ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 giống như 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 bạn trong một hằng số mảng và hỗ trợ mảng đó mang lại đối số điều kiệncủa hàm đếm gồm điều kiệnCOUNTIFS.
Ta đang dùng công thức 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 " Đã hủy " hoặc " Đang chờ cá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 ngóng xử lý","Nhập kho"))
Hình minh họa
2.3. Biện pháp đếm số trong tầm cụ thể
Công thức 1:Hàm COUNTIFS đếm những ô trong khoảng số
Công thứcCOUNTIFS2 đk sử dụng để đếm các ô giữa hai số
Để mày mò có bao nhiêu số trường đoản cú 5 đến 10 (không bao hàm 5 và 10) được chứa trong số ô từ bỏ C2 mang đến C10, hãy sử dụng 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 số đếm, hãy áp dụng công thức "lớn rộng hoặc bằng" cùng "nhỏ hơn hoặc bằng":
= COUNTIFS (B2: B10, "> = 5", B2: B10, "
Công thức 2: bí quyết COUNTIF nhằm đếm số thân X cùng Y
Bạn rất có thể áp dụng 2 bí quyết sau:
=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)– đếm tất cả bao nhiêu số lớn hơn 5 nhỏ hơn 10 vào vùng tài liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)– công thức đếm tất cả bao nhiêu số trong đoạn từ 5 mang đến 10 ở trong vùng dữ iệu C2:C10, bao hàm cả 5 và 10.
Trong đó:
- Công thức thứ nhất dùng nhằm đếm rất nhiều số to hơn giá trị giới hạn dưới (Ví dụ là 5)
- phương pháp thứ 2 thải trừ những cố lớn hơn giá trị giới hạn trên (Ví dụ là 10)
Do vậy công dụng nhận được sẽ nằm trong tầm giá trị nhận được trên.
Cách áp dụng tham chiếu ô trong bí quyết COUNTIFS
Ví dụ: mang lại bảng tài liệu sau, hãy tính các giao dịch "Apples" với số tiền >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 ý thực hiện giá trị hoàn hảo nhất trongcriteriavàcriteria_rangeđể công thức vẫn đúng khi xào nấu sang các ô tính khác.
Hình ảnh minh họa
2.4. Cách sử dụng 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 những ô bước đầu hoặc chấm dứt bằng một số ký tự độc nhất định.
- * " Được sử dụng để đếm ô chứa một từ cụ thể hoặc một k1 tự vào ô.
Ví dụ các 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, có nghĩa là cột B bao gồm chứa tên người. Hãy thêm một đk thứ hai - kết thúc Date trong cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,””&””))
Lưu ý, bạn không thể sử dụng ký tự thay mặt đại diện trong tiêu chuẩn thứ 2 do cột D đựng kiểu dữ liệu ngày mon chứ không hẳn dạng text. Đó là lý do tại sao các bạn sử dụng tiêu chuẩn “”&”” nhằm tìm ô chứa dữ liệu.
Hình minh họa
3.COUNTIFS và COUNTIF cùng với nhiều tiêu chí ngày tháng
Ví dụ 1: Đếm số ngày vào một khoảng thời hạn cụ thể
Với lấy ví dụ này, bạn cũng có thể sử dụng 2 hàm COUNTIFSnhiều điều kiện và COUNTIF.
Hình minh họa
Ví dụ 3: Đếm ngày với nhiều điều kiện dựa trên ngày hiện nay tại
Với trường đúng theo này, bạn có thể sử dụng hàm COUNTIF kết phù hợp với hàm TODAY.
Xem thêm: Làm cách nào để xóa ký tự đặc biệt trong excel
Ví dụ: dùng hàm COUNTIF để xác minh có bao nhiêu thành phầm đã được tải 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ổ cập nhất nhằm bạn thực hiện sử dụng các hàm đếm đótrong Excel, từ đó tiện lợi thống kê cùng phân tích số liệu một giải pháp hiệu quả, nâng cấp tối đa công suất công việc.
Ngoài kỹ năng và kiến thức về hàm đếm có điều kiện trong Excelcác bạn có thể tham khảo những khóahọc Excel online trên emcanbaove.edu.vn với cực kì nhiều ưu đãi bạn nhé