Hàm Đếm Giá Trị Có Điều Kiện Trong Excel, Cách Đếm Giá Trị Trong Một Trang Tính

Trong nội dung bài viết này, Học Excel Online sẽ lý giải cách thực hiện công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF trong Excel dựa bên trên thuật logic AND và OR. Các bạn sẽ tìm thấy rất nhiều ví dụ với các kiểu dữ liệu khác biệt – số, ngày tháng, text với ô cất dữ liệu.

Bạn đang xem: Hàm đếm giá trị có điều kiện

Hàm COUNTIFS – cách làm và phương pháp sử dụng

Công thức hàm COUNTIFS như sau:

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – xác định phạm vi trước tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc.

criteria1 – đặt điều kiện cho dạng số, ô tham chiếu, chuỗi văn bản, một mảng hoặc một hàm Excel khác, bắt buộc. Tiêu chí này xác minh những ô tính nào sẽ tiến hành đếm và có thể biểu diễn là 10, “

Trong thực tế, bạn không hẳn thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel đã hiển thị các đối số của hàm ngay trong lúc bạn bước đầu gõ. Đối số được nhập vào hiện tại tại sẽ tiến hành tô đậm.

COUNTIFS – các điều cần lưu lại ý:

Bạn có thể sử dụng hàm COUNTIFS để đếm các ô vào một phạm vi cá biệt với một điều kiện duy nhất cũng giống như trong nhiều vùng dữ liệu với nhiều điều kiện. Vào trường hợp đồ vật hai, chỉ gần như ô tính đáp ứng tất cả các điều kiện new được đếm.Mỗi vùng dữ liệu bổ sung phải gồm cùng số hàng với cột như vùng dữ liệu thứ nhất (đối số criteria_range1).Có thể đếm phần đa vùng tài liệu liền kề và không liền kề.Nếu tiêu chí là một tham chiếu cho một ô trống, hàm COUNTIFS cách xử trí nó như một giá bán trị bởi không (0).Bạn rất có thể sử dụng những wildcard characters trong các tiêu chí – dấu hoa thị (*) cùng dấu chấm hỏi (?).

Cách sử dụng hàm COUNTIFS với COUNTIF trong excel với tương đối nhiều tiêu chí:

Cách đếm ô tính với nhiều tiêu chí (AND logic):

Công thức 1. Bí quyết COUNTIFS với các tiêu chí khác nhau

Giả sử các bạn có một danh sách thành phầm như vào hình mặt dưới. Bạn có nhu cầu biết số sản phẩm tồn kho (giá trị cột B lớn hơn 0) nhưng lại chưa bán tốt (giá trị cột C bởi 0).

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

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

Kết quả cảm nhận là 2 (“Cherries” cùng “Lemons”):

Công thức 2. Phương pháp COUNTIFS cùng với các tiêu chí giống nhau

Khi bạn có nhu cầu đếm các mục gồm các tiêu chuẩn giống nhau, chúng ta vẫn phải hỗ trợ mỗi cặp criteria_range / criteria một bí quyết riêng lẻ.

Ví dụ: đấy là công thức đúng để đếm những mục cất 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 do chỉ bao gồm “Grapes” có mức giá trị “0” vào cả nhì cột.

Sử dụng một công thức đơn giản và dễ dàng hơn với cùng một criteria_range duy duy nhất như COUNTIFS(B2: C7, “= 0”) sẽ sở hữu lại hiệu quả khác – tổng số các ô tính trong khoảng B2:C7 cất 0 (trong lấy một ví dụ này là 4).

Cách đếm số ô tính với nhiều tiêu chí (OR logic):

Như các bạn đã thấy trong lấy một ví dụ trên, việc đếm những ô tính thỏa mãn nhu cầu tất cả các tiêu chuẩn chỉ định rất đơn giản dàng cũng chính vì hàm COUNTIFS có thiết kế để vận động theo cách này.

Nhưng nếu bạn có nhu cầu đếm các ô tất cả ít nhất 1 trong các tiêu chuẩn được chỉ định và hướng dẫn là TRUE, nghĩa là dựa trên ngắn gọn xúc tích OR thì sao? chú ý chung, gồm hai cách để thực điều này – bằng phương pháp thêm một số công thức COUNTIF hoặc thực hiện công thức SUM COUNTIFS với hằng số mảng.

Công thức 1. Thêm nhị hoặc nhiều cách làm COUNTIF hoặc COUNITFS

Trong bảng mặt dưới, trả sử bạn có nhu cầu tính các đơn đặt hàng với tinh thần “Cancelled” cùng “Pending“, chúng ta bạn chỉ hoàn toàn có thể viết 2 công thức COUNTIF bình thường và nhấn kết quả:

=COUNTIF($C$2:$C$11,”Cancelled”)+COUNTIF($C$2:$C$11,”Pending”)

Trong trường đúng theo mỗi hàm đựng nhiều hơn một điều kiện, thực hiện COUNTIFS thay vị COUNTIF. Ví dụ: để đếm số đơn đặt hàng “Cancelled” và “Pending” đối với “Apples”, áp dụng công thức sau:

=COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Cancelled”)+COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,”Pending”)

Công thức 2. Bí quyết SUM COUNTIFS với hằng mảng

Trong tình huống bạn cần thiết ập các tiêu chí, cách tiếp cận trên không phải là cách tối ưu bởi phương pháp của các bạn sẽ quá nhiều năm dòng. Để triển khai các phép tính tương tự trong một công thức nhỏ gọn hơn, hãy liệt kê toàn bộ các tiêu chí của khách hàng trong mảng và cung ứng mảng đó đến criteria của hàm COUNTIFS. Để đếm tổng, kết hợp hàm COUNTIFS bên trong hàm SUM như sau:

=SUM (COUNTIFS (range, “criteria1”, “criteria2”, “criteria3”, …))

Trong bảng mẫu của chúng tôi, nhằm đếm các đơn mua hàng có tinh thần “Cancelled“, “Pending” hoặc “In transit“, phương pháp sẽ như sau:

=SUM(COUNTIFS($C$2:$C$11,“Cancelled”,”Pending”,”In Transit”))

Tương tự, bạn cũng có thể đếm các ô dựa trên hai hoặc nhiều hơn thế các cặp criteria_range/criteria. Ví dụ: nhằm biết số lượng đơn đặt đơn hàng “Apples” trong trạng thái “Cancelled”, “Pending”, “In transit”, áp dụng công thức này:

=SUM(COUNTIFS($A$2:$A$11,”Apples”,$C$2:$C$11,“Cancelled”,”Pending”,”In transit”))

Cách đếm số trong vòng cụ thể:

Công thức 1. Hàm COUNTIFS đếm các ô trong khoảng số

Để tra cứu ra tất cả bao nhiêu số giữa 5 và 10 (không bao gồm 5 và 10) được chứa trong những ô C2 đến C10, sử dụng công thức này:

=COUNTIFS(C2:C10,”>5″,C2:C10,”=5″,B2:B10,”5″)-COUNTIF(C2:C10,”=10″) – đếm có bao nhiêu số to hơn 5 nhỏ dại hơn 10 trong vùng dữ liệu C2:C10.

Xem thêm: Phim Đam Mỹ - Tuyển Tập Phim Lgbt Âu Mỹ Hay Nhất

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm bao gồm bao nhiêu số trong đoạn từ 5 cho 10 ở trong vùng dữ iệu C2:C10, bao gồm cả 5 với 10.

Cách áp dụng tham chiếu ô trong công thức COUNTIFS

Khi sử dụng những toán tử ngắn gọn xúc tích như “>”, “=” cùng với những tham chiếu ô trong bí quyết COUNTIFS, chú ý hãy đặt các toán tử vào ngoặc kép với thêm vết (&) trước tham chiếu ô để phát hành một chuỗi văn bản.

Trong tập tài liệu mẫu dưới đây, hãy tính các giao dịch “Apples” với số tiền lớn hơn $200. Cùng với criteria_range1 vào A2: A11 cùng criteria_range2 trong B2: B11, chúng ta cũng có thể sử dụng công thức:

=COUNTIFS($A$2:$A$11,”Táo”,$B$2:$B$11,”>200″)

Hoặc, chúng ta có thể nhập các giá trị tiêu chí và mọi ô tuyệt nhất định, lấy ví dụ như F1 với F2, tiếp đến tham chiếu các ô đó trong công thức của bạn:

=COUNTIFS($A$2:$A$11,$F$1,$B$2:$B$11,”>&$F$2)

Chú ý áp dụng giá trị tuyệt đối trong criteria và criteria_range để công thức vẫn đúng khi sao chép sang những ô tính khác.

Cách thực hiện COUNTIFS với những ký tự đại diện:

Trong phương pháp Excel, bạn có thể sử dụng những ký tự thay mặt sau đây:

Dấu hỏi (?) – khớp với bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô ban đầu hoặc xong bằng một số ký tự tuyệt nhất định.

Dấu hoa thị (*) – khớp với ngẫu nhiên dãy cam kết tự nào, chúng ta dùng nó để đếm ô cất một từ cụ thể hoặc một k1 tự vào ô.

Lưu ý. Nếu bạn muốn đếm các ô cất dấu hỏi hoặc có thể dấu hoa thị, gõ dấu ngã (~) trước dấu hoa thị hoặc giấu chấm hỏi.

Giả sử bạn có danh sách các dự án trong cột A. Bạn muốn biết có bao nhiêu dự án đã ký kết, tức là cột B có chứa tên người. Hãy thêm một đk thứ hai – End date trong cột D.

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

Lưu ý, chúng ta không thể thực hiện ký tự thay mặt trong tiêu chí thứ 2 vị cột D cất kiểu tài liệu ngày mon chứ chưa phải dạng text. Đó là lý do tại sao các bạn sử dụng tiêu chuẩn “”&”” nhằm tìm ô cất dữ liệu.

COUNTIFS và COUNTIF cùng với nhiều tiêu chí ngày tháng:

Ví dụ 1. Đếm ngày trong một khoảng thời hạn cụ thể

Để đếm ngày vào một khoảng thời hạn cụ thể, chúng ta có thể sử dụng phương pháp COUNTIFS cùng với hai tiêu chuẩn hoặc phối kết hợp hai hàm COUNTIF cùng với nhau.

Ví dụ 3. Đếm ngày với khá nhiều điều kiện dựa vào ngày hiện tại tại

Bạn rất có thể sử dụng hàm TODAY kết phù hợp với COUNTIF nhằm tính những ngày dựa trên ngày hôm nay.

Ví dụ: công thức COUNTIF sau với nhì vùng dữ liệu và hai tiêu chí sẽ cho chính mình biết tất cả bao nhiêu sản phẩm đã được sở hữu nhưng không giao hàng:

=COUNTIFS(C2:C9,””&TODAY())

Công thức này còn có thể chứa đựng nhiều biến. Ví dụ: chúng ta cũng có thể tinh chỉnh nó nhằm đếm gồm bao nhiêu thành phầm được cài đặt hơn một tuần trước đó và chưa giao hàng: