Hướng dẫn cách dùng hàm đếm tổng có điều kiện countifs trong excel chi tiết nhất

Hàm COUNTIF là một hàm rất có ích trong việc thống kê, báo cáo số liệu. Vậy nên, nếu bạn không biết cách thực hiện hàm này ra làm sao thì mời chúng ta cùng Sforum xem cách áp dụng hàm đếm có đk qua các ví dụ cụ thể trong nội dung bài viết dưới trên đây nhé!

Hàm COUNTIF là gì?

Hàm COUNTIF là hàm đếm có đk trong Excel. Bạn có thể dùng nhằm đếm ô trùng lặp, đếm dữ liệu. Thường xuyên thì hàm đếm số lượng có đk này sẽ dùng với đều bảng tài liệu thống kê, yêu mong đếm số ô cất giá trị thỏa mãn nhu cầu điều kiện mang đến sẵn.

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

Công thức hàm đếm gồm điều kiện

Hàm COUNTIF trong Excel gồm công thức là =COUNTIF(range;criteria). Trong đó:

Range: Là vùng yêu cầu đếm dữ liệu.Criteria: Điều kiện đề nghị đếm. Đối số criteria buộc phải để trong vệt ngoặc kép. Không khác nhau chữ thường xuất xắc chữ hoa.

Cách sử dụng hàm COUNTIF vào Excel và ví dụ

Mời bạn tò mò cách thực hiện hàm COUNTIF qua những ví dụ tìm kiếm dữ liệu khác nhau.

Ví dụ 1: cần sử dụng hàm COUNTIF kiếm tìm kiếm số lượng tên sản phẩm Dưa hấu bao gồm trong bảng sau

Công thức như sau: =COUNTIF(B2:B8,”dưa hấu”) rồi dìm Enter để triển khai hàm.

Và kết quả sẽ đếm ra 2 giá trị có tên là Dưa hấu.

Ví dụ 2: Vẫn cùng với bảng trên, chúng ta sẽ đếm con số mặt hàng to hơn 6 kg

Công thức như sau: =COUNTIF(C2:C8,”>6″) rồi nhận Enter để thực hiện hàm.

Kết quả là đã đếm ra được là bao gồm 3 món đồ có cân nặng lớn 6kg.

Ngoài ra, so với các trường hòa hợp sau:

Đếm số lượng bé dại hơn 6kg, biến đổi đối số criteria là “Đếm số lượng to hơn hoặc bởi 6kg, biến đổi đối số criteria là “>=6”.Đếm số lượng nhỏ tuổi hơn hoặc bởi 6kg, đổi khác đối số criteria là “

Ví dụ 3: cần sử dụng hàm COUNTIF tìm con số hàng chưa hẳn là dưa hấu trong bảng

Công thức như sau: =COUNTIF(B2:B8,”Dưa hấu”) rồi nhấn Enter để tiến hành hàm. Trong số đó “” là ký kết tự khác

Kết trái đếm được là tất cả 5 món đồ không bắt buộc dưa hấu.

Ví dụ 4: cần sử dụng hàm COUNTIF search kiếm solo hàng mang tên Sầu riêng sử dụng ký tự cụ thế

Để đếm phương diện hàng bằng cách sử dụng cam kết tự sửa chữa thay thế thì sinh sống đối số criteria, các bạn điền vần âm đầu của tên mặt hàng và theo sau đó là ký tự hoa *.

*Lưu ý: giải pháp này chỉ hoàn toàn có thể áp dụng khi các sản phẩm có vần âm đầu không giống nhau.

Công thức như sau: =COUNTIF(B2:B8,”S*”) rồi nhấn Enter để triển khai hàm.

Và đây là kết quả.

Ví dụ 5: search kiếm mặt hàng có tên khác ô B3

Ở đây bọn họ sẽ tìm phần nhiều mặt hàng có tên khác ô B3 là Xoài bằng phương pháp dùng ký từ “” với “&” trước ô tham chiếu, với cú pháp hàm =COUNTIF(B2:B8,””&B3).

Kết quả sẽ ra số khía cạnh hàng và đúng là 6.

Kết luận bí quyết dùng COUNTIF – Hàm đếm có điều kiện trong Excel

Trên đây là nội dung bài viết hướng dẫn chúng ta cách áp dụng hàm COUNTIF – Hàm đếm con số có đk trong Excel. Hy vọng những tin tức này giúp ích cho bạn. Dường như bạn cũng đều có thể tìm hiểu thêm nhiều thủ thuật Excel khác tại Sforum.

Trong bài viết này, Học Excel Online sẽ giải đáp cách sử dụng công thức Hàm COUNTIFS và thực hiện Hàm COUNTIF vào Excel dựa trên thuật ngắn gọn xúc tích AND và OR. Bạn sẽ tìm thấy không hề ít ví dụ với các kiểu dữ liệu không giống nhau – số, ngày tháng, text cùng ô chứa dữ liệu.

Hàm COUNTIFS – phương pháp và bí quyết sử dụng

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

=COUNTIFS (criteria_range1, criteria1, …)

criteria_range1 – khẳng định phạm vi đầu tiên áp dụng điều kiện thứ nhất (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 chuẩn này xác định những ô tính nào sẽ tiến hành đếm và rất có thể biểu diễn là 10, “

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

COUNTIFS – hầu như điều cần lưu ý:

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

Cách thực hiện hàm COUNTIFS với COUNTIF trong excel với nhiều tiêu chí:

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

Công thức 1. Bí quyết COUNTIFS cùng 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 bên dưới. Bạn muốn biết số mặt hàng tồn kho (giá trị cột B to hơn 0) nhưng lại chưa bán tốt (giá trị 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”)

Kết quả nhận thấy là 2 (“Cherries” với “Lemons”):

Công thức 2. Cách làm COUNTIFS với các tiêu chuẩn giống nhau

Khi bạn có nhu cầu đếm những mục tất cả các tiêu chuẩn giống nhau, chúng ta vẫn phải cung cấp mỗi cặp criteria_range / criteria một cách riêng lẻ.

Ví dụ: đây là công thức đúng để đếm các mục chứa 0 cả vào cột B với 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ỉ tất cả “Grapes” có giá trị “0” trong cả nhị cột.

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

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

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

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

Công thức 1. Thêm hai hoặc nhiều phương pháp COUNTIF hoặc COUNITFS

Trong bảng bên dưới, giả sử bạn có nhu cầu tính những đơn đặt hàng với tinh thần “Cancelled” và “Pending“, các bạn bạn chỉ rất có thể viết 2 phương pháp 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 vừa lòng mỗi hàm chứa đựng nhiều hơn một điều kiện, sử dụng COUNTIFS thay vì COUNTIF. Ví dụ: để đếm số đơn đặt đơn hàng “Cancelled” và “Pending” đối với “Apples”, sử 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 cùng với hằng mảng

Trong tình huống bạn quan trọng ập nhiều tiêu chí, biện pháp tiếp cận trên chưa hẳn là giải pháp 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ự như trong một công thức bé dại gọn hơn, hãy liệt kê toàn bộ các tiêu chí của bạn trong mảng và hỗ trợ mảng đó mang lại criteria của hàm COUNTIFS. Để đếm tổng, kết hợp hàm COUNTIFS bên phía 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 những đơn mua hàng có trạng thái “Cancelled“, “Pending” hoặc “In transit“, bí quyết sẽ như sau:

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

Tương tự, bạn có thể đếm những ô dựa vào hai hoặc nhiều hơn thế nữa các cặp criteria_range/criteria. Ví dụ: để biết con số đơn đặt đơn hàng “Apples” trong tâm trạng “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 tầm cụ thể:

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

Để tìm kiếm ra bao gồm bao nhiêu số giữa 5 và 10 (không bao hàm 5 cùng 10) được chứa trong số ô C2 cho C10, áp 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ố lớn hơn 5 nhỏ dại hơn 10 vào vùng dữ liệu C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – bí quyết đếm có bao nhiêu số trong khúc từ 5 mang đến 10 trực thuộc vùng dữ iệu C2:C10, bao gồm cả 5 với 10.

Cách sử dụng tham chiếu ô trong phương pháp COUNTIFS

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

Trong tập dữ liệu mẫu dưới đây, hãy tính các deals “Apples” cùng với số tiền lớn hơn $200. Cùng với criteria_range1 vào A2: A11 và criteria_range2 trong B2: B11, bạn 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, bạn có thể nhập những giá trị tiêu chí và đa số ô độc nhất định, ví dụ như F1 và F2, tiếp đến tham chiếu đa số ô đó trong công thức của bạn:

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

Chú ý sử dụng giá trị tuyệt đối trong criteria và criteria_range để phương pháp vẫn đúng khi coppy sang những ô tính khác.

Cách sử dụng COUNTIFS với các ký tự đại diện:

Trong phương pháp Excel, chúng ta cũng 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ự solo nào, áp dụng nó nhằm đếm các ô ban đầu hoặc hoàn thành bằng một số ký tự độc nhất định.

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

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

Giả sử các bạn có danh sách các dự án trong cột A. Bạn có nhu cầu biết bao gồm bao nhiêu dự án công trình đã ký kết, có nghĩa là cột B tất cả chứa thương hiệu người. Hãy thêm một điều kiện thứ nhì – End date trong cột D.

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

Lưu ý, chúng ta không thể áp dụng ký tự đại diện trong tiêu chí thứ 2 bởi 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 chúng ta sử dụng tiêu chuẩn “”&”” để tìm ô cất dữ liệu.

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

Ví dụ 1. Đếm ngày vào một khoảng thời gian cụ thể

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

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

Bạn hoàn toàn có thể sử dụng hàm TODAY kết hợp với COUNTIF để tính những ngày dựa vào ngày hôm nay.

Xem thêm: Làm Sao Để Người Ấy Yêu Mình Hơn Không Nên Bỏ Qua, 10 Cách Khiến Nàng Thêm Yêu Bạn

Ví dụ: phương pháp COUNTIF sau với hai vùng dữ liệu và hai tiêu chí sẽ cho chính mình biết tất cả bao nhiêu thành phầm đã được mua nhưng chưa giao hàng:

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

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