hàm countif nhiều điều kiện

Microsoft Excel là ứng dụng bảng tính có tiếng bên trên toàn cầu đang rất được thật nhiều người tiêu dùng. Excel Ra đời hùn việc làm đo lường, tổng hợp của quý khách trở thành tiện lợi và dễ dàng và đơn giản rộng lớn. Trong toàn bộ những hàm Excel, hàm kiểm điểm với ĐK COUNTIFS và COUNTIF là nhì hàm tính thông thườn nhất. Sự khác lạ vượt trội nhất giữa nhì hàm này là COUNTIF được kiến thiết nhằm kiểm điểm những dù với cùng một ĐK vô một phạm vi, trong những khi COUNTIFS rất có thể nhận xét những ĐK không giống nhau vô nằm trong hoặc trong số phạm vi không giống nhau. Để khiến cho bạn gọi rất có thể nắm rõ rộng lớn về hàm kiểm điểm có không ít ĐK vô excel, mời chúng ta tìm hiểu thêm nội dung nội dung bài viết tại đây để sở hữu câu vấn đáp nhé.

1. Công thức và cách sử dụng hàm kiểm điểm có không ít ĐK vô excel COUNTIFS 

Như đang được nhắc, hàm COUNTIFS trong Excel được dùng để làm đếm số đợt xuất hiện tại độ quý hiếm vô Excel trên nhiều phạm vi dựa vào một hoặc một số trong những ĐK. Hàm COUNTIFS có trước vô Excel năm 2016, 2013, Excel 2010 và Excel 2007, nên là chúng ta cũng có thể dùng những ví dụ tiếp sau đây vô ngẫu nhiên phiên bản Excel nào.

Bạn đang xem: hàm countif nhiều điều kiện

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

Trong đó: 

- criteria_range1: Vùng chứa chấp ĐK loại nhất, đó là phần sẽ phải có

- criteria1: Điều khiếu nại loại nhất (thuộc vô vùng ĐK loại nhất) bao gồm với những dạng số, dù tham ô chiếu, chuỗi văn bạn dạng, hàm Excel hoặc vùng Excel không giống.

- criteria_range2: Vùng chứa chấp ĐK loại hai( đòi hỏi vùng criteria_range2 cần có tính rộng lớn vị nhau)

- criteria2, ... Điều khiếu nại loại nhì (thuộc vô vùng ĐK loại nhất). Quý Khách rất có thể hướng dẫn và chỉ định tối nhiều 127 cặp phạm vi / điều kiện trong công thức của tớ.

- Tương tự động vì vậy với những ĐK không giống cũng tiếp tục vận dụng vì vậy.

Công thức hàm kiểm điểm có không ít ĐK vô excel

Thực tế, chúng ta không cần thiết phải ghi nhớ cú pháp của hàm kiểm điểm với điều kiện COUNTIFS. Microsoft Excel tiếp tục hiển thị những đối số của hàm ngay lập tức khi chúng ta chính thức nhập; đối số chúng ta đang được nhập vô thời điểm hiện nay được tô đậm.

Bạn chỉ việc ghi nhớ mình muốn dùng hàm COUNTIFS trong Excel là được.

LƯU Ý: 

- Quý Khách rất có thể dùng hàm COUNTIFS vô Excel để kiểm điểm những dù vô một phạm vi với cùng một ĐK độc nhất rưa rứa trong không ít phạm vi với tương đối nhiều ĐK. Nếu về sau, chỉ những dù đáp ứng tất cả những ĐK được hướng dẫn và chỉ định mới mẻ được xem.

- Criteria1 bạn rất có thể trình diễn bọn chúng như là giống như những ví dụ: 10, "<=32", A4, "gord"... 

- Mỗi phạm vi bổ sung cập nhật cần với nằm trong số mặt hàng và cột như phạm vi trước tiên.Cả phạm vi tiếp giáp và ko ngay lập tức kề đều được được chấp nhận.

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

- Quý Khách rất có thể dùng những ký tự động đại diện trong ĐK - lốt hoa thị (*) và lốt căn vặn (?).

Sử dụng hàm COUNTIFS vô Excel nhằm kiểm điểm với điều kiện

>>> Xem thêm: Trọn cỗ tin cẩn học tập văn chống cơ bạn dạng kể từ giáo viên tiên phong hàng đầu ưu đãi chỉ còn 99k: Xem bên trên đây

2. Cách dùng hàm COUNTIFS trong Excel

Dưới phía trên các bạn sẽ nhìn thấy một số trong những ví dụ về công thức minh họa cơ hội dùng những hàm COUNTIFS vô Excel nhằm nhận xét nhiều ĐK.

Thông qua chuyện những ví dụ được minh họa tiếp sau đây các bạn sẽ tưởng tượng rời khỏi được cơ hội dùng những hàm này trong số tình huống ví dụ ứng nhằm rất có thể vận dụng hàm COUNTIFS vô Excel nhằm nhận xét nhiều ĐK không giống nhau

2.1. Cách kiểm điểm dù tính với tương đối nhiều ĐK (AND logic)

Công thức 1. Công thức COUNTIFS với tương đối nhiều ĐK không giống nhau

Ví dụ bạn với cùng một list thành phầm như hiển thị vô hình họa chụp screen bên dưới. Quý Khách mong muốn đã có được một số trong những lượng những món đồ vô kho (giá trị vô cột B to hơn 0) tuy nhiên không được buôn bán (giá trị là cột C vị 0).

Khi này chúng ta triển khai bằng phương pháp dùng công thức kiểm điểm với điều kiện:

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

Và con số là 2 ("Anh khoét " và " Chanh "):

Công thức hàm Countifs

Công thức 2. Công thức COUNTIFS với nằm trong tiêu xài chí

Khi mình muốn kiểm điểm những mục với điều kiện giống hệt nhau, chúng ta vẫn cần thiết hỗ trợ từng cặp điều kiện_range / điều kiện .

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

Công thức hàm COUNTIFS với nằm trong tiêu xài chí

2.2. Cách kiểm điểm dù có không ít ĐK không giống nhau (logic OR)

Như chúng ta đang được thấy trong số ví dụ bên trên, việc kiểm điểm những dù đáp ứng nhu cầu toàn bộ những ĐK đang được hướng dẫn và chỉ định là dễ dàng và đơn giản vì như thế hàm COUNTIF nhiều điều kiện được kiến thiết nhằm hoạt động và sinh hoạt Theo phong cách này.

Nhưng nếu như mình muốn kiểm điểm những dù tuy nhiên tối thiểu một trong số ĐK được hướng dẫn và chỉ định là TRUE , tức là dựa vào logic OR thì sao? Nhìn cộng đồng, với nhì phương pháp để triển khai việc này - bằng phương pháp thêm thắt một số trong những công thức COUNTIFS hoặc dùng công thức SUM COUNTIFS.

Công thức 1. Thêm nhì hoặc nhiều công thức vô COUNTIFS

Ví dụ: để sở hữu được con số đơn đặt mua " Đã diệt " và " Đang ngóng xử lý" mang lại " 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 ngóng xử lý")

Công thức kiểm điểm dù tính có không ít điều kiện

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

Trong những trường hợp khi chúng ta cần nhận xét thật nhiều ĐK thì cách bên trên ko cần là cơ hội tốt nhất có thể vì như thế công thức của các bạn sẽ vượt lên trên nhiều năm. 

Để triển khai những phép tắc tính tương tự động vô một công thức không gồ gề rộng lớn, hãy liệt kê toàn bộ những điều kiện của chúng ta vô một hằng số mảng và hỗ trợ mảng tê liệt mang lại đối số điều kiện của hàm kiểm điểm với điều kiện COUNTIFS. 

Ta tiếp tục sử dụng công thức sau:

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

Trong bảng tiếp sau đây, nhằm kiểm điểm ‘Táo” với hiện trạng " Đã diệt " hoặc " Đang ngóng xử lý " hoặc " Nhập kho ", công thức tiếp tục như sau:

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

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

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

Ngoài những cách sử dụng hàm kiểm điểm có không ít ĐK vô excel như bên trên đang được share, chúng ta cũng rất có thể dùng hàm COUNTIFS nhằm kiểm điểm số trong tầm thời hạn ví dụ. Đối với yếu tố này thì sẽ sở hữu 2 công thức cơ bạn dạng nhất, ví dụ tê liệt là:

2.3.1. Công thức 1: Hàm COUNTIFS kiểm điểm những dù trong tầm số

Công thức COUNTIFS 2 ĐK dùng nhằm kiểm điểm những dù thân thích nhì số

Để lần hiểu với từng nào số kể từ 5 cho tới 10 (không bao hàm 5 và 10) được chứa chấp trong số dù kể từ C2 cho tới C10, hãy dùng công thức này:

= COUNTIFS (C2: C10, "> 5", C2: C10, "<10")

Hàm COUNTIFS kiểm điểm những dù trong tầm số

Để kiểm điểm những số 5 và 10 vô số kiểm điểm, hãy dùng công thức "lớn rộng lớn hoặc bằng" và "nhỏ rộng lớn hoặc bằng":

= COUNTIFS (B2: B10, "> = 5", B2: B10, "<= 10").

2.3.2. Công thức COUNTIF nhằm kiểm điểm số thân thích X và Y

Bạn rất có thể vận dụng 2 công thức sau: 

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″) – kiểm điểm với từng nào số to hơn 5 nhỏ rộng lớn 10 vô vùng tài liệu C2:C10.

Xem thêm: chúc sinh nhật em trai

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức kiểm điểm với từng nào số trong khúc kể từ 5 cho tới 10 nằm trong vùng tài liệu C2:C10, bao hàm cả 5 và 10.

Trong đó:

- Công thức trước tiên dùng để làm kiểm điểm những số to hơn độ quý hiếm số lượng giới hạn bên dưới (Ví dụ là 5)

- Công thức thứ hai vô hiệu những cố to hơn độ quý hiếm số lượng giới hạn bên trên (Ví dụ là 10)

Do vậy thành quả sẽ có được tiếp tục ở trong tầm độ quý hiếm sẽ có được bên trên. 

>>> Xem thêm: Cách kiểm điểm số đợt xuất hiện tại ký tự động vô Excel giản dị và đơn giản và chuẩn chỉnh nhất

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

Trong những câu thức hàm kiểm điểm có không ít ĐK vô excel, chúng ta cũng có thể dùng những ký tự động thay mặt đại diện như sau:

- ? : được dùng nhằm kiểm điểm những dù chính thức hoặc kết đốc vị một số trong những ký tự động chắc chắn.

- * " được dùng nhằm kiểm điểm dù có một kể từ ví dụ hoặc một k1 tự động vô dù.

Ví dụ chúng ta với list những dự án công trình vô cột A. Quý Khách mong muốn biết với từng nào dự án công trình đã ký kết kết, tức là cột B với chứa chấp thương hiệu người. Hãy thêm 1 ĐK loại nhì - End Date vô cột D.

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

Lưu ý, chúng ta ko thể dùng ký tự động thay mặt đại diện vô tiêu chuẩn thứ hai vì như thế cột D chứa chấp loại tài liệu tháng ngày chứ không cần cần dạng text. Đó là nguyên do vì sao chúng ta dùng tiêu chuẩn “<>”&”” nhằm lần dù chứa chấp tài liệu.

Hàm COUNTIFS với những ký tự động đại diện

3. Cách dùng hàm COUNTIFS theo ĐK ngày tháng

Hàm countifs rất có thể khiến cho bạn thực hiện được thật nhiều việc, vượt trội vô này là việc dùng hàm countifs theo dõi ĐK tháng ngày nhằm kiểm điểm ngày. Có 3 phương pháp để chúng ta cũng có thể sử dụng hàm countifs kiểm điểm thời hạn, vượt trội tê liệt là:

3.1. Đếm ngày vô một khoảng tầm thời hạn cụ thể

Với ví dụ này, chúng ta cũng có thể dùng 2 hàm COUNTIFS nhiều ĐK và COUNTIF.

Ví dụ, công thức sau được dùng để làm kiểm điểm số ngày vô dù C2-C10 với thời hạn kể từ 1/6/2014 - 7/7/2014:

=COUNTIFS(C2:C9,”=6/1/2014″,C2:C9,”<=6/7/2014″)

=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)

Công thức đếm ngày vô một khoảng tầm thời hạn cụ thể

3.2. Đếm ngày với tương đối nhiều điều kiện

Để kiểm điểm ngày trong số cột không giống nhau kể từ 2 ĐK trở lên trên, chúng ta cũng có thể dùng hàm COUNTIFS. 

Ví dụ: Tìm rời khỏi coi với từng nào thành phầm được mua sắm sau 20/5 và phó 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 kiểm điểm ngày với tương đối nhiều điều kiện

3.3. Đếm ngày với tương đối nhiều ĐK dựa vào ngày hiện tại tại

Với tình huống này, chúng ta cũng có thể dùng hàm COUNTIFS kết phù hợp với hàm TODAY. 

Ví dụ: Dùng hàm COUNTIFS để xác lập với từng nào thành phầm và đã được mua sắm tuy nhiên ko phó hàng:

=COUNTIFS(C2:C9,”<“&TODAY(),D2:D9,”>”&TODAY())

Công thức kiểm điểm ngày dựa vào ngày hiện tại tại

4. Hàm kiểm điểm vô excel với ĐK COUNTIFS phối hợp SUM vô Excel

Về cơ bạn dạng hàm COUNTIFS cũng ko không giống gì hàm COUNTIF là bao nhiêu, chẳng qua chuyện phương thức dùng của chính nó với phần phức tạp rộng lớn. Nếu mình muốn dùng hàm COUNTIFS và SUM nhằm kiểm điểm với ĐK vô excel thì chúng ta triển khai như sau:

Ví dụ: Lập bảng trong excel chứa chấp vấn đề về sắc tố táo và độ dài rộng của bọn chúng. Mục tiêu xài sau cuối của ví dụ này này là kiểm điểm con số táo đỏ au size rộng.

Lập bảng vô excel

Tiếp theo dõi chúng ta lựa chọn dù mong muốn hiển thị thành 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, excel đang được kiểm điểm cho mình con số táo size rộng hoặc táo với red color.

Kết trái khoáy kiểm điểm cuối cùng

5. Cách sửa những lỗi thông thường bắt gặp khi sử dụng hàm kiểm điểm với ĐK vô excel

Trong quy trình sử dụng hàm kiểm điểm với ĐK to hơn và nhỏ rộng lớn người tiêu dùng đặc biệt hoặc bắt gặp lỗi. Sau đó là một số trong những lỗi thông thường bắt gặp nằm trong cơ hội xử lý cho mình tìm hiểu thêm, đánh dấu ngay lập tức nhé.

5.1. COUNTIFS ko hoạt động và sinh hoạt Khi kiểm điểm những độ quý hiếm text

Khi kiểm điểm những độ quý hiếm text, nhằm hoạt động và sinh hoạt bọn chúng cần được điền vô vào lốt ngoặc kép. Nếu ko được chèn, hàm COUNTIFS sẽ không còn thể kiểm điểm được, lúc không kiểm điểm được chuỗi văn bạn dạng tiếp tục trả về độ quý hiếm 0.

Ví dụ tiếp sau đây text ko bịa vô lốt ngoặc kép nên Search Engine Results Page vị 0

Kết trái khoáy trả về vị 0 tự sai công thức

Để xử lý lại này chúng ta chỉ việc nhập lại công thức với lốt ngoặc ở text là được. Công thức: =COUNTIFS(E5:E12, "Car")

Công thức đúng vào khi kiểm điểm độ quý hiếm text

5.2. COUNTIFS ko hoạt động và sinh hoạt vì như thế sai tham ô chiếu phạm vi

Khi sử dụng hàm kiểm điểm có không ít ĐK vô excel chúng ta đặc biệt hoặc bắt gặp lỗi hàm COUNTIFS ko hoạt động và sinh hoạt vì như thế sai tham ô chiếu phạm vi. Vì vậy khi sử dụng nhiều hơn thế một tiêu chuẩn vô hàm COUNTIFS, phạm vi của dù tiêu chuẩn không giống cần với nằm trong con số. Nếu ko hàm COUNTIFS tiếp tục lỗi.

Ví dụ: Bạn mong muốn kiểm điểm doanh thu buôn bán xe hơi ở Austin. Công thức chúng ta nhập vô bảng tê liệt là =COUNTIFS(E5:E12,"Car",D5:D11,"Austin"). Quan sát kỹ công thức, các bạn sẽ thấy phạm vi tiêu chuẩn trước tiên ở đó là E5:E12 tuy nhiên phạm vi ở tiêu chuẩn loại nhì là D5:D11. Khi số dù vô phạm vi mang lại tiêu chuẩn rất khác nhau thì thành quả tiếp tục trả về lỗi #VALUE!.

Công thức nhập vô bảng tiêu chuẩn rất khác nhau

Cách xử lý này là chúng ta nhập lại công thức thành =COUNTIFS(E5:E12,"Car",D5:D12,"Austin"). Như vậy mới mẻ trả về thành quả chính được.

5.3. COUNTIFS ko hoạt động và sinh hoạt vì như thế lỗi vô công thức

Trong quy trình thao tác làm việc với COUNTIFS nếu như không chèn công thức đầu tiên thì chắc hẳn rằng sẽ ảnh hưởng lỗi hàm ko hoạt động và sinh hoạt. Nếu chúng ta sử dụng toán tử học tập, ví dụ như: lốt to hơn (>), nhỏ rộng lớn (<), vị (=), không vị (<>), thì cả toán tử và tiêu chuẩn số đều phải được nhập vô phía bên trong và một phương trình.

Ví dụ: Quý Khách đang muốn lần doanh thu bán sản phẩm to hơn 100.000USD, chúng ta nhập công thức là: =COUNTIFS(F5:F12,">" 100000). Khi này chúng ta mới chỉ chèn toán tử phía bên trong phương trình chứ không tồn tại tiêu chuẩn số nên thành quả lỗi hiển thị " “There’s a problem with this formula”.

Cách xử lý này là chúng ta nhập công thức: =COUNTIFS(F5:F12,">100000") => Enter. Khi đã nhập cả nhì toán tử và tiêu chuẩn phía bên trong lốt ngoặc thì các bạn sẽ sẽ có được thành quả chính như ý.

Kết trái khoáy trả về Khi nhập chính công thức

6. Những vấn đề cần cảnh báo khi sử dụng hàm COUNTIFS 

Dưới đó là một số trong những vấn đề cần lưu ý khi sử dụng hàm COUNTIFS, các bạn hãy ghi ghi nhớ nhé:

- Các vùng lựa chọn bổ sung cập nhật cần được với nằm trong số mặt hàng và cột với vùng criteria_range1. Nếu ko, hàm COUNTIFS tiếp tục trả về thành quả sai.

- Điều khiếu nại vùng lựa chọn tham ô chiếu cho tới dù trống trải thì hàm COUNTIFS tự động hóa coi độ quý hiếm là 0.

- cũng có thể sử dụng những ký tự động đại diện— lốt căn vặn (?) để lần tìm kiếm những độ quý hiếm ko xác lập. Ví dụ, công thức =COUNTIFS(A2:A10,"A") tiếp tục kiểm điểm toàn bộ những dù vô phạm vi A2:A10 với chứa chấp chữ "A" ở ngẫu nhiên địa điểm này.

Xem thêm: cách chèn logo vào ảnh

- Lưu ý rằng hàm COUNTIFS ko phân biệt chữ hoa chữ thông thường. Ví dụ, công thức =COUNTIFS(A2:A10,"a") tiếp tục kiểm điểm toàn bộ những dù vô phạm vi A2:A10 với chứa chấp chữ "a" hoặc "A".

7. Kết luận

Trên đó là toàn cỗ vấn đề về hàm kiểm điểm có không ít ĐK vô excel tuy nhiên Unica đang được tổ hợp được. Hàm kiểm điểm với ĐK là cơ hội thông thườn nhất nhằm chúng ta triển khai thao tác kiểm điểm vô excel giản dị và đơn giản, kể từ tê liệt dễ dàng và đơn giản tổng hợp và phân tách số liệu một cơ hội hiệu suất cao, nâng cao tối nhiều hiệu suất việc làm. Ngoài kiến thức và kỹ năng về hàm kiểm điểm với ĐK vô Excel các chúng ta cũng có thể tìm hiểu thêm những khóa học Excel online bên trên Unica với siêu nhiều ưu đãi chúng ta nhé

Tags: Excel