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

Microsoft Excel là ứng dụng bảng tính phổ biến bên trên toàn cầu đang rất được thật nhiều người tiêu dùng. Excel Thành lập chung việc làm đo lường, đo đếm của người xem trở thành thuận tiện và đơn giản dễ dàng rộng lớn. Trong toàn bộ những hàm Excel, hàm điểm đem ĐK COUNTIFS và COUNTIF là nhì hàm tính phổ biến nhất. Sự khác lạ tiêu biểu vượt trội nhất giữa nhì hàm này là COUNTIF được kiến thiết nhằm điểm những dù mang trong mình một ĐK vô một phạm vi, trong những lúc COUNTIFS rất có thể Reviews những ĐK không giống nhau vô nằm trong hoặc trong số phạm vi không giống nhau. Để giúp đỡ bạn phát âm rất có thể nắm rõ rộng lớn về hàm điểm có rất nhiều ĐK vô excel, mời chúng ta xem 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 điểm có rất nhiều ĐK vô excel COUNTIFS 

Như tiếp tục trình bày, hàm COUNTIFS trong Excel được dùng để làm đếm số chuyến 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 trong những ĐK. Hàm COUNTIFS có trước vô Excel năm nhâm thìn, 2013, Excel 2010 và Excel 2007, bởi vậy 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, đấy là phần cần 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 đem những dạng số, dù tham lam 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 bởi 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 hàng rất có thể chỉ định và hướng dẫn 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 điểm có rất nhiều Đ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 điểm đem đ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 này được tô đậm.

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

>>> Tất cả đều sở hữu vô cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

LƯU Ý: 

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

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

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

- Nếu điều kiện là một tham lam 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 hàng rất có thể dùng những ký tự động đại diện trong ĐK - lốt hoa thị (*) và lốt chất vấn (?).

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

>>> Xem thêm: Trọn cỗ tin tưởng 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ỉ với 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 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 Reviews nhiều ĐK.

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

2.1. Cách đ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 mang trong mình một list thành phầm như hiển thị vô hình họa chụp màn hình hiển thị bên dưới. quý khách hàng ham muốn đạt được một trong những lượng những sản phẩm vô kho (giá trị vô cột B to hơn 0) tuy nhiên không được chào bán (giá trị là cột C bởi vì 0).

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

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

Và con số là 2 ("Anh đục " và " Chanh "):

Công thức hàm Countifs

Công thức 2. Công thức COUNTIFS đem nằm trong chi phí chí

Khi bạn thích điểm những mục đem đ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ụ: đấy là công thức đích nhằm điểm những mục đem 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ì thế chỉ " Nho " có mức giá trị "0" vô cả nhì cột

Công thức hàm COUNTIFS đem nằm trong chi phí chí

2.2. Cách điểm dù có rất nhiều ĐK không giống nhau (logic OR)

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

Nhưng nếu như bạn thích điểm những dù nhưng mà tối thiểu một trong số ĐK được chỉ định và hướng dẫn là TRUE , tức là dựa vào logic OR thì sao? Nhìn công cộng, đem nhì phương pháp để triển khai việc này - bằng phương pháp thêm thắt một 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 " Đã bỏ " và " Đang hó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 hóng xử lý")

Công thức điểm dù tính có rất nhiều đ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 Reviews 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ì thế công thức của các bạn sẽ vượt lên trên lâu năm. 

Để triển khai những quy tắc tính tương tự động vô một công thức nhỏ và gọn 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 bại mang lại đối số điều kiện của hàm điểm đem đ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 điểm ‘Táo” đem hiện trạng " Đã bỏ " hoặc " Đang hó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 hóng xử lý","Nhập kho"}))

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

2.3. Cách điểm số trong vòng cụ thể

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

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

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

Để dò thám hiểu đem 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 điểm những dù trong vòng số

Để điểm những số 5 và 10 vô số đ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").

Xem thêm: rơi tự do là một chuyển động

2.3.2. Công thức COUNTIF nhằ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″) – điểm đem từng nào số to hơn 5 nhỏ rộng lớn 10 vô vùng tài liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức điểm đem 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 thứ nhất dùng để là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 hóa 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 sản phẩm có được tiếp tục ở trong vòng độ quý hiếm có được bên trên. 

>>> Xem thêm: Cách điểm số chuyến xuất hiện tại ký tự động vô Excel giản dị 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 điểm có rất nhiều ĐK vô excel, chúng ta cũng có thể dùng những ký tự động thay mặt như sau:

- ? : được dùng nhằm điểm những dù chính thức hoặc kết thúc đẩy bởi vì một trong những ký tự động chắc chắn.

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

Ví dụ chúng ta đem list những dự án công trình vô cột A. quý khách hàng ham muốn biết đem từng nào dự án công trình đã ký kết kết, tức là cột B đem 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 vô tiêu chuẩn thứ hai vì thế cột D chứa chấp loại tài liệu tháng ngày chứ không hề cần dạng text. Đó là nguyên nhân vì sao chúng ta dùng tiêu chuẩn “<>”&”” nhằm dò thám 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ể giúp đỡ bạn thực hiện được thật nhiều việc, tiêu biểu vượt trội vô này đó là việc dùng hàm countifs bám theo ĐK tháng ngày nhằm điểm ngày. Có 3 phương pháp để chúng ta cũng có thể sử dụng hàm countifs điểm thời hạn, tiêu biểu vượt trội bại 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 đ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

Để đ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 đi ra coi đem từng nào thành phầm được mua sắm sau 20/5 và uỷ thác 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 đ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 đem từng nào thành phầm đang được mua sắm tuy nhiên ko uỷ thác hàng:

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

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

4. Hàm điểm vô excel đem ĐK COUNTIFS phối kết 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 quýt phương thức dùng của chính nó đem phần phức tạp rộng lớn. Nếu bạn thích dùng hàm COUNTIFS và SUM nhằm điểm đem Đ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à độ cao thấp của bọn chúng. Mục chi phí sau cùng của ví dụ này này đó là điểm con số táo đỏ gay cỡ rộng.

Lập bảng vô excel

Tiếp bám theo chúng ta lựa chọn dù ham muốn hiển thị sản phẩm 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 tiếp tục điểm cho chính mình con số táo cỡ rộng hoặc táo đem red color.

Kết trái ngược đ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 điểm đem ĐK vô excel

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

5.1. COUNTIFS ko sinh hoạt Lúc điểm những độ quý hiếm text

Khi điểm những độ quý hiếm text, nhằm 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ể điểm được, lúc không đ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 đặt điều vô lốt ngoặc kép nên SERP bởi vì 0

Kết trái ngược trả về bởi vì 0 vì thế 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 đem lốt ngoặc ở text là được. Công thức: =COUNTIFS(E5:E12, "Car")

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

5.2. COUNTIFS ko sinh hoạt vì thế sai tham lam chiếu phạm vi

Khi sử dụng hàm điểm có rất nhiều ĐK vô excel chúng ta vô cùng hoặc bắt gặp lỗi hàm COUNTIFS ko sinh hoạt vì thế sai tham lam chiếu phạm vi. Vì vậy khi sử dụng nhiều hơn thế nữa 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 đem nằm trong con số. Nếu ko hàm COUNTIFS tiếp tục lỗi.

Ví dụ: Bạn ham muốn điểm lợi nhuận chào bán xe hơi ở Austin. Công thức chúng ta nhập vô bảng bại 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 thứ nhất ở đấy 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ì sản phẩm 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ề sản phẩm đích được.

5.3. COUNTIFS ko sinh hoạt vì thế lỗi vô công thức

Trong quy trình thao tác với COUNTIFS còn nếu không chèn công thức đầu tiên thì chắc hẳn rằng có khả năng sẽ bị lỗi hàm ko 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 (<), bởi vì (=), không bởi vì (<>), thì cả toán tử và tiêu chuẩn số đều phải được nhập vô bên phía trong và một phương trình.

Ví dụ: quý khách hàng đang muốn dò thám lợi nhuận 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ử bên phía trong phương trình chứ không tồn tại tiêu chuẩn số nên sản phẩm 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 bên phía trong lốt ngoặc thì các bạn sẽ có được sản phẩm đích như yêu cầu.

Kết trái ngược trả về Lúc nhập đích công thức

6. Những vấn đề cần chú ý khi sử dụng hàm COUNTIFS 

Dưới đấy là một 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 đem 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ề sản phẩm sai.

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

Xem thêm: ngay via than tai 2023

- cũng có thể sử dụng những ký tự động đại diện— lốt chất vấn (?) để dò thá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 điểm toàn bộ những dù vô phạm vi A2:A10 đem chứa chấp chữ "A" ở ngẫu nhiên địa điểm này.

- 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 điểm toàn bộ những dù vô phạm vi A2:A10 đem chứa chấp chữ "a" hoặc "A".

7. Kết luận

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