Hàm Đếm Tổng Có Điều Kiện

     

Trong tất cả các hàm Excel, hàm đếm có đk COUNTIFS cùng COUNTIF chắc rằng thường dễ bị nhầm lẫn nhất vì chúng trông siêu giống nhau và cả hai đều cùng gồm mục đíchdùng nhằm đếm các ô dựa trên các điều kiệnđã được chỉ định.

Bạn đang xem: Hàm đếm tổng có điều kiện

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

Nhằm giúp cho bạn đọc tách biệt một cách đúng mực và nắm được cách sử dụng hai hàm đếm có đk COUNTIFSđúng trong Excel, trong bài viết này runclub.vn sẽ giới thiệu và phía dẫn bạn đọc cách áp dụng và chọn lựa công thức tác dụng nhất mang đến từng trách nhiệm từng câu hỏi cụ thể, nhất là đối cùng với hàmCOUNTIFS.

1. Hàm COUNTIFS của Excel - cú pháp và cách sử dụng

Như sẽ đề cập, hàm COUNTIFS của Excel được dùng làm đếm các ô trên những phạm vi dựa trên một hoặc một số trong những điều kiện. Hàm COUNTIFS tất cả sẵn vào Excel 2016, 2013, Excel 2010 với Excel 2007, vì vậy chúng ta có thể sử dụng những ví dụ dưới đây trong bất kỳ phiên bảnExcelnào.

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

Trong đó:

- criteria_range1: Vùng chứa đk 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ó 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 điều kiện thứ hai( yêu cầu vùng criteria_range2 phải tất cả độ lớn bằng 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 về tối đa 127 cặp phạm vi / điều kiệntrong phương pháp của mình.

- tựa như như vậy với những điều khiếu nại khác cũng sẽ á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 có điều kiệnCOUNTIFS. Microsoft Excel đã hiển thị những đối số của hàm ngay lúc bạn bắt đầu nhập; đối số bạn đang nhập vào hôm nay được sơn đậm.

Bạn chỉ cần nhớ bạn có nhu cầu sử dụng hàm COUNTIFS trongExcel là được.

LƯU Ý:

- bạn cũng có thể sử dụng hàm COUNTIFS trong 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 tương tự trong nhiều phạm vi với tương đối nhiều điều kiện. Nếu sau này, chỉ số đông ô thỏa mãn nhu cầu tất cả các điều kiện được chỉ định bắt đầu được tính.

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

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

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

2. Cách áp dụng hàm đếm có điều kiện COUNTIFS với rất nhiều điều kiện

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

Thông qua số đông 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 số trường hợp rõ ràng tương ứng để hoàn toàn có thể áp dụng hàmCOUNTIFS vào Excel để đánh giá nhiều điều kiện khác nhau

2.1. Biện pháp đếm các ô có khá nhiều điều kiện

Công thức 1. Cách làm COUNTIFS với nhiều điều kiện không giống nhau

Ví dụbạn tất cả một danh sách thành 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ố lượng các món đồ trong kho (giá trị trong cột B lớn hơn 0) nhưng chưa được bán (giá trị là cột C bằng 0).

Nhiệm vụ có thể được thực hiện bằng phương pháp sử dụng cách làm này:

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

Và số lượng là 2 ("Anh đào " và " Chanh "):

*

Hình minh họa

Công thức 2. Bí quyết COUNTIFS gồm cùng tiêu chí

Khi bạn muốn đếm các mục có đ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 nhằm đếm các mục gồm 0 cả trong 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 bởi chỉ " Nho " có mức giá trị "0" vào cả nhị cột

*

Hình minh họa

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

Như bạn đã thấy trong các ví dụ trên, vấn đề đếm những ô thỏa mãn nhu cầu tất cả các điều khiếu nại đã chỉ định là thuận tiện vì hàm đếm có điều kiệnCOUNTIFS được thiết kế để vận động theo giải pháp này.

Xem thêm: Các Chủ Đề Thi Nói Tiếng Anh, 7 Chủ Đề Thi Nói Tiếng Anh B1 Thường Gặp

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

Công thức 1. Thêm hai hoặc nhiều cách làm trong COUNTIFS

Ví dụ: để sở hữu được số lượng đơn đặt hàng " Đã diệt " với " Đang đợi xử lý" mang lại " táo khuyết ", 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 chờ 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 đánh giá rất nhiều điều kiện,cách trên chưa hẳn là cách tốt nhất vì bí quyết của bạn sẽ quá dài.

Để tiến hành các phép tính tương tự trong một công thức nhỏ tuổ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, để đếm ‘Táo” tất cả trạng thái " Đã bỏ " hoặc " Đang chờ xử trí " 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 hóng xử lý","Nhập kho"))

*

Hình minh họa

2.3. Cách đếm số trong khoảng cụ thể

Công thức 1:Hàm COUNTIFS đếm những ô trong tầm số

Công thứcCOUNTIFS để đếm những ô giữa hai số

Để khám phá có bao nhiêu số từ 5 mang đến 10 (không bao hàm 5 với 10) được chứa trong số ô trường đoản cú C2 mang đến C10, hãy áp 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 những đếm, hãy sử dụng công thức "lớn rộng hoặc bằng" cùng "nhỏ rộng hoặc bằng":

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

Công thức 2: cách làm COUNTIF nhằm đếm số thân X với Y

Bạn rất có thể áp dụng 2 cách làm sau:

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”=10″)– đếm bao gồm bao nhiêu số lớn hơn 5 bé dại hơn 10 trong vùng tài liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″)– công thức đếm có bao nhiêu số trong đoạn từ 5 cho 10 ở trong vùng dữ iệu C2:C10, bao hàm cả 5 với 10.

Trong đó:

- Công thức trước tiên dùng để đếm đầy đủ số to hơn giá trị giới hạn dưới (Ví dụ là 5)

- công thức thứ 2 loại bỏ những cố lớn hơn giá trị giới hạn trên (Ví dụ là 10)

Do vậy hiệu quả nhận được đang nằm trong khoảng giá trị cảm nhận trên.

Cách thực hiện tham chiếu ô trong phương pháp COUNTIFS

Ví dụ: đến bảng dữ liệu sau, hãy tính các đơn hàng "Apples" cùng 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 ý sử dụng giá trị tuyệt đối trongcriteriavàcriteria_rangeđể phương pháp vẫn đúng khi xào luộc sang các ô tính khác.

*

Hình ảnh minh họa

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

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

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

- * " Được sử dụng để đếm ô cất một từ cụ thể hoặc một k1 tự trong ô.

Ví dụ chúng ta có danh sách những dự án trong cột A. Bạn muốn biết tất cả bao nhiêu dự án công trình đã ký kết kết, có nghĩa là cột B tất cả chứa tên người. Hãy thêm một điều kiện thứ nhị - over Date trong cột D.

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

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

*

Hình minh họa

3.COUNTIFS với COUNTIF 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 ví dụ này, chúng ta 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

Với trường phù hợp này, chúng ta có thể sử dụng hàm COUNTIF kết hợp với hàm TODAY.

Xem thêm: Tải Nhạc Chuông Không Lời Dễ Thương Hay Nhất, Nhạc Chuông Không Lời Dễ Thương

Ví dụ: dùng hàm COUNTIF để xác định có bao nhiêu sản phẩm đã được mua nhưng chưa 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à những cách thịnh hành nhất để bạn triển khai sử dụng các hàm đếm đótrong Excel, tự đó dễ dãi thống kê với phân tích số liệu một cách hiệu quả, cải thiện tối đa hiệu suất công việc.

Ngoài kiến thức và kỹ năng về hàm đếm có đk các chúng ta có thể tham khảo các khóahọc Excel trực tuyến tại runclub.vn với cực kì nhiều ưu đãi các bạn nhé