Top 2 # Xem Nhiều Nhất Cách Dùng Countifs Trong Excel Mới Nhất 1/2023 # Top Like | Beiqthatgioi.com

Cách Dùng Countif Trong Excel

Microsoft Excel cung cấp rất nhiều hàm nhằm mục đích đếm những loại ô khác nhau, chẳng hạn như ô trống hoặc ô không trống, số, ngày tháng hoặc giá trị chữ, chứa các từ hoặc ký tự cụ thể, v.v …

Trong bài này, Học Excel Online sẽ tập trung vào hàm COUNTIF được dùng để đếm các ô với điều kiện chỉ định. Trước tiên, chúng ta sẽ trình bày ngắn gọn về cú pháp và cách sử dụng chung, và sau đó là một số ví dụ và cảnh báo về những điều có thể xảy ra khi sử dụng hàm này với nhiều điều kiện và các ô đặc thù.

Hàm COUNTIF – cú pháp và cách sử dụng:

Hàm COUNTIF được sử dụng để đếm các ô trong một phạm vi quy định đáp ứng một tiêu chí, hoặc điều kiện nhất định.

Ví dụ: bạn có thể viết công thức COUNTIF để tìm ra có bao nhiêu ô trong bảng tính của bạn chứa số lớn hơn hoặc nhỏ hơn số bạn chỉ định. Hoặc để đếm các ô với một từ cụ thể hoặc bắt đầu với một (các) chữ cái cụ thể.

Cú pháp của hàm COUNTIF rất đơn giản:

COUNTIF(range, criteria)

Như bạn thấy, chỉ có 2 đối số, cả hai đều được yêu cầu:

Range – xác định một hoặc nhiều ô để đếm. Bạn đặt phạm vi trong công thức như bạn thường làm trong Excel, ví dụ: A1: A20.

Và đây là ví dụ đơn giản nhất về hàm COUNTIF. Những gì bạn thấy trong hình dưới là danh sách những tay vợt giỏi nhất trong 14 năm qua. Công thức = COUNTIF (C2: C15, “Roger Federer”) tính số lần Roger Federer có tên trong danh sách:

Ví dụ về hàm COUNTIF

Như bạn đã thấy, cú pháp của hàm COUNTIF rất đơn giản. Tuy nhiên, nó cho phép nhiều biến thể của điều kiện, bao gồm các ký tự đại diện, giá trị của ô khác, và thậm chí các hàm Excel khác. Tính đa dạng này làm cho hàm COUNTIF thực sự mạnh mẽ và phù hợp với nhiều nhiệm vụ, như bạn sẽ thấy trong các ví dụ tiếp theo.

Hàm COUNTIF cho văn bản và số (chính xác tuyệt đối)

Vùng chọn đếm là tham số đầu tiên;

Dấu phẩy như dấu phân cách;

Một từ hoặc một số từ trong dấu ngoặc kép làm điều kiện.

Thay vì nhập lại từ, bạn có thể sử dụng tham chiếu đến bất kỳ ô nào chứa từ hoặc các từ đó và nhận được kết quả tương tự, ví dụ: = COUNTIF (C1: C9, C7).

Hc Excel nâng cao

Hàm COUNTIF với ký tự đại diện (khớp một phần)

Trong trường hợp dữ liệu Excel của bạn bao gồm một vài biến thể của (các) từ khoá mà bạn muốn tính, thì bạn có thể sử dụng ký tự đại diện để đếm tất cả các ô chứa một từ, cụm từ hoặc chữ cái nhất định như một phần của nội dung ô.

Giả sử bạn có một danh sách các nhiệm vụ được giao cho những người khác nhau và bạn muốn biết số nhiệm vụ được giao cho Danny Brown. Bởi vì tên của Danny được viết bằng nhiều cách khác nhau, ta nhập “* Brown *” là điều kiện = COUNTIF (D2: D10, “*Brown *”).

Tự động hóa Excel bằng VBA cho người mới

Đếm các ô bắt đầu hoặc kết thúc bằng các ký tự nhất định

Bạn có thể sử dụng ký tự đại diện, dấu sao (*) hoặc dấu chấm hỏi (?), với tùy thuộc vào kết quả mà bạn muốn.

Nếu bạn muốn biết số ô bắt đầu hoặc kết thúc bằng văn bản nhất định bất kể có bao nhiêu ký tự khác trong ô, hãy sử dụng các công thức sau:

= COUNTIF (C2: C10, “Mr*”) – tính các ô bắt đầu bằng “Mr”.

= COUNTIF (C2: C10, “* ed”) – tính các ô kết thúc bằng chữ “ed”.

= COUNTIF (D2: D9, “??own”) – đếm số ô kết thúc bằng chữ “own” và có chính xác 5 ký tự trong các ô từ D2 đến D9, bao gồm cả khoảng cách.

= COUNTIF (D2: D9, “Mr ??????”) – đếm số ô bắt đầu bằng chữ “Mr” và có chính xác 8 ký tự trong các ô từ D2 đến D9, bao gồm khoảng cách.

Mẹo. Để tìm số ô chứa dấu hỏi “?” hoặc dấu hoa thị “*”, hãy gõ dấu ngã (~) trước dấu ? hoặc * trong công thức. Ví dụ, = COUNTIF (D2: D9, “* ~? *”) để đếm tất cả các ô có chứa dấu chấm hỏi trong dải D2: D9.

Hàm COUNTIF cho ô trống và không trống

Các ví dụ sau thể hiện cách bạn có thể sử dụng hàm COUNTIF trong Excel để đếm số ô trống hoặc không trống trong một phạm vi được chỉ định.

Trong một số hướng dẫn hàm COUNTIF khác, bạn có thể thấy công thức để tính các ô không trống trong Excel như thế này:

= COUNTIF (range, “*”)

Nhưng thực tế là, công thức trên chỉ đếm các ô có chứa bất kỳ giá trị văn bản nào, có nghĩa là các ô có ngày tháng và số sẽ được coi là các ô trống và không được đếm!

Nếu bạn cần một công thức tổng quát hàm COUNTIF để đếm tất cả các ô không phải là ô trống trong một phạm vi xác định, hãy dùng:

Công thức tổng quát hàm COUNTIF cho khoảng trống (tất cả các loại giá trị): = COUNTIF (range, “”)

Công thức trên đếm số, ngày và giá trị văn bản. Ví dụ, công thức = COUNTIF (C2: C11, “”) trả về số của tất cả các ô trống trong dải C2: C11.

Đếm khoảng trống: = COUNTBLANK (C2: C11)

Đếm không khoảng cách: = ROWS (C2: C11) * COLUMNS (C2: C11) -COUNTBLANK (C2: C11)

COUNTIF lớn hơn, nhỏ hơn hoặc bằng:

Hãy lưu ý rằng trong các công thức COUNTIF, một dấu so sánh với một số luôn luôn được đặt trong dấu ngoặc kép.

Bạn cũng có thể sử dụng tất cả các công thức trên để đếm các ô dựa trên giá trị ô khác, chỉ cần thay thế số trong các tiêu chí bằng ô tham chiếu.

Sử dụng hàm COUNTIF với ngày:

Đếm một ngày cụ thể

=COUNTIF(B2:B10,”6/1/2014″)

Đếm số ô trong vùng B2:B10 có ngày 6/1/2014

Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng 6/1/2014

Đếm số ô trong vùng B2:B10 có ngày lớn hơn hoặc bằng ngày trong ô B2 trừ đi 7 ngày

Ngoài những công thức phổ biến này, bạn có thể sử dụng hàm COUNTIF kết hợp với các hàm Excel ngày và giờ cụ thể như TODAY () để đếm các ô dựa trên ngày hiện tại.

Điều kiện

Công thức ví dụ

Đếm ngày bằng với ngày hiện tại

=COUNTIF(A2:A10,TODAY())

Đếm ngày nhỏ hơn ngày hiện hành, ví dụ hôm nay

=COUNTIF(A2:A10,”<“&TODAY())

Đếm ngày sau ngày hiện hành, ví dụ sau hôm nay

Đếm ngày sau ngày hôm nay 1 tuần

=COUNTIF(A2:A10,”=”&TODAY()+7)

Đếm ngày trong một khoảng thời gian

= COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”)

Hãy chú ý đến ký tự đại diện (*) trong điều kiện thứ hai, nó được sử dụng để đếm tất cả các loại nước trái cây trong danh sách.

= COUNTIF (B2: B13, “Lemonade”) + COUNTIF (B2: B13, “* juice”) + COUNTIF (B2: B13, “Ice cream”)

Sử dụng chức năng COUNTIF để tìm các giá trị lặp và giá trị duy nhất:

Một công dụng khác của hàm COUNTIF là để tìm các giá trị lặp trong một cột, giữa hai cột hoặc trong một hàng.

Tìm hiểu thêm: Học excel kế toán bán hàng

Ví dụ 2. Đếm số lần lặp giữa hai cột

Nếu bạn có hai danh sách riêng biệt, ví dụ danh sách tên trong các cột B và C, và bạn muốn biết có bao nhiêu tên xuất hiện trong cả hai cột, bạn có thể sử dụng hàm COUNTIF kết hợp với chức năng SUMPRODUCT để đếm số lần lặp:

Thậm chí ta có thể đếm bao nhiêu tên duy nhất trong Cột C, tức là các tên không xuất hiện trong Cột B:

Đếm số lần lặp trong một hàng:

Đếm các giá trị duy nhất trong một hàng:

So sánh hàm COUNTIFS với SUMPRODUCT trong việc đếm theo nhiều điều kiện Cách dùng hàm COUNTIFS và COUNTIF với nhiều điều kiện 9 bí kíp sử dụng COUNTIF hiệu quả trong Excel

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Cách Dùng Hàm Countifs Trong Excel

Hàm CountIFS trong excel hoạt động giống như cách tìm tập con của 2 hay nhiều tập hợp có cùng độ lớn các phần tử mà ta đã học ở lớp 10.

Cú pháp như sau:

Hàm này được hiểu là tìm điểm chung giữa các phần tử trong một tập hợp có cùng độ lớn theo một điều kiện cho sẵn.

Ta thấy hàm này có cặp đối số thứ 2: [criteria_range2, criteria2] nằm trong ngoặc có nghĩa là cặp đối số này là tùy chọn, khi hàm này chỉ có một cặp đối số thì cách sử dụng như Hàm CountIF ở bài học trước.

Tuy nhiên ta có thể sử dụng 127 cặp criteria_range và criteria trong nội hàm này.

A9:=Countifs(B3:D3,10),Criteria_Range1: B3:D3

Hoạt động: Đếm xem học sinh Nguyễn Văn A có mấy môn đạt được điểm mười (10) và kết quả là không có môn nào đạt 10 điểm cả.Ở đây chỉ có một cặp đối số nên lúc này hàm này có cách dùng như hàm CountIF.

A11:=Countifs(B3:B6,9,C3:C6,9,D3:D6,10)

Trong đó

Criteria_Range1 : B3:B6Criteria1 : 9

Criteria_Range2 : C3:C6​Criteria2 : 9

Criteria_Range3 : D3:D6

​Criteria3 : 10

Hoạt động: Hàm sẽ so sánh giá trị các cặp B3 & C3; B4 & C4;B5 & C5; B6 & C6; có cùng Criteria là 9; và kết quả là có 2 học sinh Nguyễn Văn A (Cặp B3 & C3) và Nguyễn văn C (cặp B5 & C5) sau đó nó xét tiếp cặp B3 & C3 với D3; và cặp B5 & C5 với D5, có điểm 10;Kết quả đánh giá 3 tiêu chí như sau: Chỉ có 1 học sinh có 2 điểm 9 ở môn Toán và Lý và điểm 10 môn Hóa.

►Số phần tử của Criteria_Range phải bằng nhau, nếu không thì hàm sẽ trả về #VALUE.►Các Criteria_Range, phải cung tính chất: Cùng là Cột, Hoặc cùng là dòng như bảng bên trên, tôi đã tô màu theo từng Criteria_range để phân biệt, ColB; Col C, Col D hoặc Row 3, Row 4, Row 5, Row 6

Ví dụ:=Countifs(B3:B7,9,C3:C6,9,D3:D6,10) : #VALUE.=Countifs(B3:B7,9,C3:C6,9,C3:D7,10) : #VALUE.=Countifs(B4:B7,9,C5:C8,9,D2:D5,10) : 0

Hướng Dẫn Cách Dùng Hàm Countifs Trong Excel Qua Ví Dụ

Hướng dẫn cách sử dụng hàm COUNTIFS trong Excel

Hàm COUNTIFS là hàm cơ bản nên bạn có thể sử dụng nó trong hầu hết các phiên bản Excel như Excel 2016, Excel 2013, Excel 2010, Excel 2007 và cả Excel cho MAC, iPhone, Android. Trong bài viết này mình sẽ sử dụng Excel 2016 để làm ví dụ, nếu bạn đang dùng phiên bản Excel khác thì cũng làm tương tự.

Cú pháp của hàm COUNTIFS

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

Các giá trị trong hàm COUNTIFS

criteria_range1: Vùng chọn đầu tiên cần thống kê. Đây là giá trị bắt buộc

criteria1: Điều kiện áp dụng cho vùng chọn criteria_range1. Giá trị này có thể là một số, tham chiếu ô, biểu thức hoặc văn bản. Đây là giá trị bắt buộc.

[criteria_range2, criteria2]: Các cặp vùng chọn và điều kiện bổ sung. Hàm COUNTIFS cho phép tối đa 127 cặp vùng chọn và điều kiện. Đây là giá trị tùy chọn.

Lưu ý khi sử dụng hàm COUNTIFS

Mỗi vùng chọn bổ sung (criteria_range2, criteria_range3,…) phải có cùng số hàng và cột với vùng chọn 1 (criteria_range1) và các vùng chọn không nhất thiết phải liền kề nhau.

Điều kiện áp dụng của mỗi vùng chọn sẽ được áp dụng cho một ô mỗi lần.

Nếu điều kiện của vùng chọn tham chiếu đến 1 ô trống thì hàm COUNTIFS coi ô trống là giá trị 0.

Trong hàm COUNTIFS, bạn có thể dùng các ký tự đại diện như dấu hỏi (?) thay cho bất kỳ ký tự đơn nào và dấu sao (*) thay cho bất kỳ chuỗi ký tự nào trong điều kiện. Khi bạn cần tìm 1 dấu chấm hỏi (?) hay dấu sao (*) thực thì hãy gõ dấu ngã (~) ở trước ký tự đó.

Để thực hành các ví dụ về hàm COUNTIFS chúng ta sẽ thực hành với bảng số liệu như sau

Để sử dụng công thức hàm COUNTIFS với nhiều điều kiện thì yêu cầu đặt ra là: Thống kê số lượng nhà cung cấp không bán được sản phẩm Samsung A5 nào.

Để giải bài toán này chúng ta sẽ có 2 điều kiện:

Điều kiện 1: Tìm số lượng nhà cung cấp bán sản phẩm Samsung A5

Dựa vào bảng đã cho thì ta sẽ có vùng chọn là C4:C13 và điều kiện là ” Samsung A5 “

Điều kiện 2: Tìm số lượng nhà cung cấp không bán được sản phẩm nào. Tức là số lượng bán bằng 0

Dựa vào bảng ta có vùng chọn là F4:F13 và điều kiện là ” 0 “

Dựa vào công thức hàm COUNTIFS và những phân tích ở trên ta sẽ có công thức tính như sau:

=COUNTIFS(C4:C13,”Samsung A5″,F4:F13,”0″)

Kết quả thống kê bằng 1. Bạn hoàn toàn có thể đếm bằng mắt thường để kiểm tra lại kết quả.

Trong nhiều trường hợp bạn sẽ phải kết hợp nhiều tiêu chí, khi đó công thức của bạn sẽ rất dài. Để công thức ngắn gọn hơn bạn hãy liệt kê tất cả các tiêu chí trong mảng, sau đó đưa mảng đó vào điều kiện của hàm COUNTIFS. Và để đếm tổng thì chỉ việc kết hợp hàm COUNTIFS với hàm SUM với công thức như sau:

=SUM(COUNTIFS(criteria_range,{“criteria1″,”criteria2″,”criteria3”,…}))

Áp dụng vào trong bảng tính ở trên, chúng ta sẽ đếm tổng số các sản phẩm không bán được và chưa thống kê được với công thức như sau:

=SUM(COUNTIFS(F4:F13,{“0″,”~?”}))

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

Như đã nói trong phần lý thuyết ở đầu bài, trong bảng tính Excel sẽ có lúc có những ô chứ ký tự đơn hoặc chuỗi ký tự. Và để thống kê các ô như vậy với hàm COUNTIFS bạn sẽ sử dụng các ký tự đại diện để đếm.

Dấu hỏi chấm (?) – đại diện cho bất kỳ ký tự đơn nào, sử dụng nó để đếm các ô bắt đầu hoặc kết thúc bằng một số ký tự nhất định.

Dấu sao (*) – đại diện cho bất kỳ chuỗi ký tự nào, bạn dùng nó để đếm ô chứa một từ cụ thể hoặc một ký tự trong ô.

Nếu muốn đếm các ô có chứa dấu hỏi chấm (?) hoặc dấu sao (*) thì bạn chỉ việc thêm dấu ngã (~) ở trước dấu hỏi chấm hoặc dấu sao.

Ví dụ chúng ta có bảng với các ký tự, chuỗi ký tự và số như hình dưới. Dựa vào đó chúng ta sẽ thống kê số lượng ô chứa ký tự đơn, ô chứa chuỗi ký tự và tìm dấu hỏi chấm, dấu sao thực.

Download bài tập về hàm COUNTIFS trong Excel

[symple_callout fade_in=”false” button_text=”Tải về” button_url=”https://1drv.ms/u/s!AnIwW-EZfINLhYYcdxf_P55A8Tn07w” button_color=”teal” button_size=”large” button_border_radius=”2px” button_target=”blank” button_rel=”nofollow” button_icon_left=”” button_icon_right=””]Download bài tập về hàm COUNTIFS trong Excel[/symple_callout]

Hướng Dẫn Cách Dùng Hàm Đếm Có Điều Kiện Countifs Trong Excel

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

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

Nhằm giúp bạn đọc phân biệt một cách chính xác và nắm được cách sử dụng hai hàm đếm có điều kiện COUNTIFS đúng trong Excel, trong bài viết này Unica sẽ giới thiệu và hướng dẫn bạn đọc cách sử dụng và lựa chọn công thức hiệu quả nhất cho từng nhiệm vụ từng bài toán cụ thể, đặc biệt là đối với hàm COUNTIFS.

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

criteria_range1: Vùng chứa điều kiện 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) gồm có các 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 có độ lớn bằng nhau)

criteria2, … Điều kiện thứ hai (thuộc trong vùng điều kiện thứ nhất). Bạn có thể chỉ định tối đa 127 cặp phạm vi / điều kiện trong công thức của mình.

Tương tự như vậy với các điều kiện khác cũng sẽ áp dụng như vậy.

Bạn có thể sử dụng hàm COUNTIFS trong Excel để đếm các ô trong một phạm vi với một điều kiện duy nhất cũng như trong nhiều phạm vi với nhiều điều kiện. Nếu sau này, chỉ những ô đáp ứng tất cả các điều kiện được chỉ định mới được tính.

Criteria1 bạn có thể biểu diễn chúng giống như các ví dụ: 10, “<=32”, A4, “gord”…

Mỗi phạm vi bổ sung phải có cùng số hàng và cột như phạm vi đầu tiên.Cả phạm vi tiếp giáp và không liền kề đều được cho phép.

Nếu điều kiện là một tham chiếu đến một ô trống , hàm COUNTIFS coi nó là giá trị 0 (0).

Bạn có thể sử dụng các ký tự đại diện trong điều kiện – dấu hoa thị (*) và dấu hỏi (?).

2 Cách sử dụng hàm đếm có điều kiện COUNTIFS với nhiều điều kiện

Công thức 1. Công thức COUNTIFS với nhiều điều kiện khác nhau

Ví dụ bạn có một danh sách sản 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ặt hàng 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 cách sử dụng công thức này:

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

Khi bạn muốn đếm các mục có điều kiện giống hệt nhau, bạn vẫn cần cung cấp từng cặp điều kiện_range / điều kiện .

Ví dụ: đây là công thức đúng để đếm các mục có 0 cả trong cột B và cột C:

Nhưng nếu bạn muốn đếm các ô mà ít nhất một trong các điều kiện được chỉ định là TRUE , tức là dựa trên logic OR thì sao? Nhìn chung, có hai cách để thực hiện việc này – bằng cách thêm một số công thức COUNTIFS hoặc sử dụng công thức SUM COUNTIFS.

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

Ví dụ: để có được số lượng đơn đặt hàng ” Đã hủy ” và ” Đang chờ xử lý” cho ” Táo “, hãy sử dụng công thức sau:

Trong các tình huống khi bạn phải đánh giá rất nhiều điều kiện, cách trên không phải là cách tốt nhất vì công thức của bạn sẽ quá dài.

Để tìm hiểu có bao nhiêu số từ 5 đến 10 (không bao gồm 5 và 10) được chứa trong các ô từ C2 đến C10, hãy sử dụng công thức này:

Để đếm các số 5 và 10 trong số đếm, hãy sử dụng công thức “lớn hơn hoặc bằng” và “nhỏ hơn hoặc bằng”:

Bật mí khóa học “Trở thành cao thủ Excel trong 10 giờ”

Khóa học “Trở thành cao thủ Excel trong giờ”

Khóa học giảng dạy theo hình thức trực tuyến dành cho bạn có nhu cầu tìm học công cụ xử lý số liệu Excel một cách bài bản, chi tiết, đầy đủ và tính ứng dụng cao cho cả quá trình học tập và công việc. Không những thế, khóa học sẽ giúp bạn nắm vững các hàm excel thông dụng và đặc biệt nhất. Từ đó, dễ dàng thống kê và 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.