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 canh ty việc làm đo lường và tính toán, đo đếm của quý khách trở thành tiện lợi và đơn giản dễ dàng rộng lớn. Trong toàn bộ những hàm Excel, hà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 design nhằm điểm những dù với cùng 1 ĐK nhập một phạm vi, trong những khi COUNTIFS rất có thể reviews những ĐK không giống nhau nhập nằm trong hoặc trong số phạm vi không giống nhau. Để giúp đỡ bạn gọi rất có thể làm rõ rộng lớn về hàm điểm có tương đối nhiều ĐK nhập 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 điểm có tương đối nhiều ĐK nhập 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 nhập 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 nhập Excel năm 2016, 2013, Excel 2010 và Excel 2007, chính vì vậy bạn cũng có thể dùng những ví dụ sau đây nhập ngẫu nhiên phiên bản Excel nào.
Bạn đang xem: hàm countifs 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 cần phải có
- criteria1: Điều khiếu nại loại nhất (thuộc nhập 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 phiên bản, 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 nên có tính rộng lớn bởi vì nhau)
- criteria2, ... Điều khiếu nại loại nhì (thuộc nhập vùng ĐK loại nhất). quý khách rất có thể hướng đẫn tối nhiều 127 cặp phạm vi / điều kiện trong công thức của tôi.
- 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ó tương đối nhiều ĐK nhập excel
Thực tế, chúng ta không nhất thiết phải ghi nhớ cú pháp của hà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 tức thì khi chúng ta chính thức nhập; đối số chúng ta đang được nhập nhập thời điểm hiện tại đượ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 phải sở hữu nhập 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 rất có thể dùng hàm COUNTIFS nhập Excel để điểm những dù nhập một phạm vi với cùng 1 ĐK có một không hai gần giống trong vô số nhiều phạm vi với khá nhiều ĐK. Nếu sau đây, chỉ những dù đáp ứng tất cả những ĐK được hướng đẫn mới nhất được xem.
- Criteria1 bạn rất có thể trình diễn bọn chúng tương đương tựa như các ví dụ: 10, "=32", A4, "gord"...
- Mỗi phạm vi bổ sung cập nhật nên với 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 cho phép.
- Nếu điều kiện là một tham ô chiếu cho tới một ô trống rỗng , 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 - vết hoa thị (*) và vết chất vấn (?).
Sử dụng hàm COUNTIFS nhập Excel nhằ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ơ phiên bản kể từ giáo viên số 1 ư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 trong những ví dụ về công thức minh họa cơ hội dùng những hàm COUNTIFS nhập Excel nhằm reviews nhiều ĐK.
Thông qua chuyện những ví dụ được minh họa 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 ví dụ ứng nhằm rất có thể vận dụng hàm COUNTIFS nhập Excel nhằm reviews nhiều ĐK không giống nhau
2.1. Cách điểm dù tính với khá nhiều ĐK (AND logic)
Công thức 1. Công thức COUNTIFS với khá nhiều ĐK không giống nhau
Ví dụ bạn với cùng 1 list thành phầm như hiển thị nhập hình ảnh chụp màn hình hiển thị bên dưới. quý khách ham muốn dành được một trong những lượng những món đồ nhập kho (giá trị nhập 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 với điều kiện:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
Và con số là 2 ("Anh khoan " và " Chanh "):
Công thức hàm Countifs
Công thức 2. Công thức COUNTIFS với nằm trong xài chí
Khi bạn thích đ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ằm điểm những mục với 0 cả nhập 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" nhập cả nhì cột
Công thức hàm COUNTIFS với nằm trong xài chí
2.2. Cách điểm dù có tương đối 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ù đáp ứng nhu cầu toàn bộ những ĐK tiếp tục hướng đẫn là đơn giản dễ dàng vì như thế hàm COUNTIF nhiều điều kiện được design nhằm hoạt động và sinh hoạt Theo phong cách này.
Nhưng nếu như bạn thích điểm những dù tuy nhiên tối thiểu một trong số ĐK được hướng đẫn 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 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 nhập COUNTIFS
Ví dụ: để sở hữu được con số đơn đặt mua " Đã bỏ " và " Đang đợi xử lý" mang đến " 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 đợi xử lý")
Công thức điểm dù tính có tương đối 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 nên reviews thật nhiều ĐK thì cách bên trên ko nên là cơ hội rất tốt vì như thế công thức của các bạn sẽ quá lâu năm.
Để triển khai những luật lệ tính tương tự động nhập một công thức thon 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 nhập một hằng số mảng và hỗ trợ mảng cơ mang đến đối số điều kiện của hà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 sau đây, nhằm điểm ‘Táo” với hiện trạng " Đã bỏ " hoặc " Đang đợi 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 đợi xử lý","Nhập kho"}))
SUMIFS với hằng số mảng
2.3. Cách điểm số trong tầm cụ thể
Ngoài những cách sử dụng hàm điểm có tương đối nhiều ĐK nhập 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 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ơ phiên bản nhất, ví dụ cơ là:
2.3.1. Công thức 1: Hàm COUNTIFS điểm những dù trong tầm số
Công thức COUNTIFS 2 ĐK dùng nhằm điểm những dù thân ái nhì số
Để thăm dò 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 điểm những dù trong tầm số
Để điểm những số 5 và 10 nhập 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: câu bị đông là gì tiếng việt
2.3.2. Công thức COUNTIF nhằm điểm số thân ái 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 với từng nào số to hơn 5 nhỏ rộng lớn 10 nhập vùng tài liệu C2:C10.
=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – công thức đ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 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 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ả cảm nhận được tiếp tục ở trong tầm độ quý hiếm cảm nhận được bên trên.
>>> Xem thêm: Cách điểm số chuyến xuất hiện tại ký tự động nhập 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ó tương đối nhiều ĐK nhập excel, bạn 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 đôn đốc 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ừ ví dụ hoặc một k1 tự động nhập dù.
Ví dụ chúng ta với list những dự án công trình nhập cột A. quý khách ham 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 nhập cột D.
=COUNTIFS(B2:B10,”*”,D2:D10,”>”&””))
Lưu ý, chúng ta ko thể dùng ký tự động thay mặt nhập 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 hề nên dạng text. Đó là nguyên do vì sao chúng ta dùng tiêu chuẩn “>”&”” nhằm thăm dò 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, vượt trội nhập này là việc dùng hàm countifs theo đuổi ĐK tháng ngày nhằm điểm ngày. Có 3 phương pháp để bạn cũng có thể sử dụng hàm countifs điểm thời hạn, vượt trội cơ là:
3.1. Đếm ngày nhập một khoảng tầm thời hạn cụ thể
Với ví dụ này, bạn 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 nhập 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 nhập một khoảng tầm thời hạn cụ thể
3.2. Đếm ngày với khá 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, bạn cũng có thể dùng hàm COUNTIFS.
Ví dụ: Tìm đi ra coi với từng nào thành phầm được mua sắm sau 20/5 và giao 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 điểm ngày với khá nhiều điều kiện
3.3. Đếm ngày với khá nhiều ĐK dựa vào ngày hiện tại tại
Với tình huống này, bạn 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 giao phó 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 nhập excel với ĐK COUNTIFS phối kết hợp SUM nhập Excel
Về cơ phiên bản 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 pháp dùng của chính nó với 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 với ĐK nhập 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 xài ở đầu cuối của ví dụ này này là điểm con số táo đỏ loét size lớn.
Lập bảng nhập excel
Tiếp theo đuổi chúng ta lựa chọn dù ham 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 tiếp tục điểm cho mình con số táo size lớn hoặc táo với red color.
Kết trái khoáy điểm cuối cùng
5. Cách sửa những lỗi thông thường gặp gỡ khi sử dụng hàm điểm với ĐK nhập excel
Trong quy trình sử dụng hàm điểm với ĐK to hơn và nhỏ rộng lớn người tiêu dùng cực kỳ hoặc gặp gỡ lỗi. Sau đó là một trong những lỗi thông thường gặp gỡ nằm trong cơ hội xử lý cho mình tìm hiểu thêm, ghi lại tức thì nhé.
5.1. COUNTIFS ko hoạt động và sinh hoạt Khi điểm những độ quý hiếm text
Khi điểm những độ quý hiếm text, nhằm hoạt động và sinh hoạt bọn chúng nên được điền nhập vào vế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 phiên bản tiếp tục trả về độ quý hiếm 0.
Ví dụ sau đây text ko bịa đặt nhập vết ngoặc kép nên SERP bởi vì 0
Kết trái khoáy 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 với vế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 hoạt động và sinh hoạt vì như thế sai tham ô chiếu phạm vi
Khi sử dụng hàm điểm có tương đối nhiều ĐK nhập excel chúng ta cực kỳ hoặc gặp gỡ 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 nhập hàm COUNTIFS, phạm vi của dù tiêu chuẩn không giống nên với 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 doanh thu chào bán xe hơi ở Austin. Công thức chúng ta nhập nhập bảng cơ 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 ở đó là E5:E12 tuy nhiên phạm vi ở tiêu chuẩn loại nhì là D5:D11. Khi số dù nhập phạm vi mang đến 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 nhập 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 nhất trả về thành quả đích được.
5.3. COUNTIFS ko hoạt động và sinh hoạt vì như thế lỗi nhập 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 chắ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ư: vế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 nhập bên phía trong và một phương trình.
Ví dụ: quý khách đang muốn thăm dò 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ử bên phía 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 bên phía trong vết ngoặc thì các bạn sẽ cảm nhận được thành quả đích như yêu cầu.
Kết trái khoáy trả về Khi nhập đích công thức
6. Những vấn đề cần Note khi sử dụng hàm COUNTIFS
Dưới đó 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 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 rỗng thì hàm COUNTIFS tự động hóa coi độ quý hiếm là 0.
Xem thêm: cấu trúc thì quá khứ đơn
- cũng có thể sử dụng những ký tự động đại diện— vết chất vấn (?) để thăm dò tìm tòi 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ù nhập phạm vi A2:A10 với chứa chấp chữ "A" ở ngẫu nhiên địa điểm nào là.
- 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ù nhập 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 điểm có tương đối nhiều ĐK nhập excel tuy nhiên Unica tiếp tục tổ hợp được. Hà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 điểm nhập excel giản dị, kể từ cơ đơ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 với ĐK nhập Excel các bạn 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 biết bao ưu đãi chúng ta nhé
Bình luận