Cách dùng hàm COUNTIF kết hợp hàm LEFT, MID, RIGHT trong Excel & Google Sheets

Trong quá trình xử lý dữ liệu, việc đếm theo ký tự đầu, ký tự giữa hay ký tự cuối của chuỗi là nhu cầu phổ biến. Khi đó, hàm COUNTIF kết hợp hàm LEFT, MID, RIGHT trở thành công cụ cực kỳ hữu ích trong Excel và Google Sheets. Bài viết này sẽ hướng dẫn bạn chi tiết cách sử dụng, kèm các ví dụ thực tế, cùng tham khảo ngay nhé. 

Tổng quan cơ bản chung về hàm COUNTIF

Trước khi tìm hiểu hàm COUNTIF kết hợp hàm LEFT, bạn cần phải nắm rõ kiến thức cơ bản về hàm. Nếu bạn hiểu rõ bản chất của COUNTIF, bạn sẽ dễ dàng xây dựng các công thức nâng cao và tránh được nhiều lỗi sai phổ biến.

Hàm COUNTIF là gì?

Hàm COUNTIF là hàm thuộc nhóm hàm thống kê. Hàm này có nhiệm vụ đếm số lượng ô trong một phạm vi dữ liệu đáp ứng một điều kiện cụ thể. Khác với hàm COUNT (chỉ đếm ô chứa số) và hàm COUNTA (đếm tất cả ô có dữ liệu). Hàm COUNTIF có khả năng đếm có chọn lọc theo điều kiện mà bạn đặt ra.

Hàm COUNTIF giúp bạn đếm số lượng ô trong một vùng dữ liệu thỏa mãn một điều kiện xác định. Hàm này đặc biệt hữu ích khi bạn làm việc với danh sách lớn như danh sách khách hàng, mã sản phẩm, bảng lương, bảng điểm hoặc dữ liệu bán hàng.

Trong thực tế, COUNTIF thường được dùng để:

• Thống kê số lần xuất hiện của giá trị cụ thể
• Đếm số ô chứa văn bản xác định
• Đếm số lớn hơn, nhỏ hơn hoặc bằng một giá trị
• Đếm theo điều kiện có ký tự đại diện (*, ?)

Tuy nhiên, khi điều kiện nằm ở một phần của chuỗi (ví dụ chỉ ký tự đầu, ký tự giữa hoặc ký tự cuối), bạn sẽ cần kết hợp thêm các hàm xử lý chuỗi như LEFT, MID hoặc RIGHT.

*Lưu ý quan trọng: Hàm COUNTIF chỉ xử lý một điều kiện duy nhất. Nếu bạn cần nhiều điều kiện, bạn phải sử dụng COUNTIFS.

Cú pháp và các tham số của hàm COUNTIF

Để sử dụng đúng hàm COUNTIF, bạn cần ghi nhớ cú pháp chuẩn như sau:

=COUNTIF(range, criteria)

Trong đó: 

• range (vùng dữ liệu): Là phạm vi các ô mà bạn muốn kiểm tra và đếm. Ví dụ: A2:A100.
• criteria (điều kiện): Là điều kiện để xác định ô nào sẽ được đếm. 

Điều kiện có thể là Một số (100); Một biểu thức (“>100”); Một văn bản (“Hà Nội”); Một tham chiếu ô (B2); Một điều kiện có ký tự đại diện (“A*”).

*Lưu ý quan trọng:

• Nếu điều kiện là văn bản hoặc có chứa toán tử logic như >, <, >=, <=, bạn phải đặt trong dấu ngoặc kép “”.
• Hàm COUNTIF bỏ qua sự khác biệt giữa chữ hoa và chữ thường.

Ví dụ thực tế áp dụng hàm COUNTIF

Để giúp bạn hiểu rõ hơn, chúng ta cùng đi qua một ví dụ cụ thể. Bạn có danh sách mã sản phẩm và muốn đếm số lượng mã bắt đầu bằng “SP”.

*Yêu cầu: Đếm số mã sản phẩm bắt đầu bằng “SP”.

Công thức:

=COUNTIF(A2:A6;”SP*”)

Trong đó:

• A2:A6 là vùng dữ liệu cần đếm.
• “SP*” nghĩa là chuỗi bắt đầu bằng “SP”, sau đó có thể có bất kỳ ký tự nào.

Kết quả: 

Hàm LEFT, MID, RIGHT – Bộ công cụ xử lý chuỗi quan trọng

Nếu hàm COUNTIF là công cụ dùng để đếm theo điều kiện, thì hàm LEFT, MID và RIGHT chính là công cụ giúp bạn tách đúng phần dữ liệu cần kiểm tra trước khi đếm. Dưới đây là cách từng hàm hoạt động một cách cụ thể, có ví dụ minh họa rõ ràng và dễ hiểu, dễ áp dụng. 

Hàm LEFT – Tách ký tự bên trái

Hàm LEFT là hàm dùng để trích xuất một số lượng ký tự nhất định tính từ phía bên trái của một chuỗi văn bản. Cú pháp chuẩn của hàm LEFT như sau:

=LEFT(text, num_chars)

Trong đó:

• text là ô chứa dữ liệu hoặc chuỗi văn bản mà bạn muốn trích xuất.
• num_chars là số lượng ký tự mà bạn muốn lấy, tính từ bên trái sang phải.
• Nếu bạn không nhập đối số num_chars, hệ thống sẽ mặc định lấy 1 ký tự đầu tiên.

Ví dụ, bạn có bảng dữ liệu như bên dưới đây:

*Yêu cầu: Bạn cần tách mã phòng ban là 2 ký tự đầu tiên của mỗi mã nhân viên.

Công thức:

=LEFT(A2;2)

Trong đó:

• Hàm LEFT sẽ lấy 2 ký tự đầu tiên trong ô A2.
• Nếu ô A2 chứa giá trị “KT202301”, hàm sẽ trả về kết quả là “KT”.
• Kết quả mà hàm LEFT trả về luôn có định dạng văn bản, kể cả khi phần được cắt ra là số.

Kết quả: 

Hàm RIGHT – Lấy ký tự từ phía bên phải

Hàm RIGHT là hàm dùng để trích xuất một số lượng ký tự tính từ phía bên phải của chuỗi văn bản. Cú pháp của hàm RIGHT như sau:

=RIGHT(text, num_chars)

Trong đó:

• text là chuỗi hoặc ô chứa dữ liệu cần lấy ra.
• num_chars là số lượng ký tự cần lấy từ bên phải sang trái.

Ví dụ, bạn có một bảng dữ liệu dưới đây:

*Yêu cầu: Bạn cần lấy 2 ký tự cuối của mỗi mã đơn hàng để xác định mã chi nhánh.

Công thức:

=RIGHT(A2;2)

Trong đó:

• Hàm RIGHT sẽ lấy 2 ký tự cuối cùng của chuỗi trong ô A2.
• Nếu ô A2 chứa giá trị “DH-HCM-09”, kết quả trả về sẽ là “09”.
• Bạn có thể sử dụng phần dữ liệu này để thống kê số đơn hàng theo từng chi nhánh.

Kết quả: 

Hàm MID – Tách ký tự ở giữa chuỗi

Nếu LEFT xử lý phần đầu và RIGHT xử lý phần cuối, thì MID là hàm giúp bạn tách dữ liệu tại bất kỳ vị trí nào trong chuỗi. Hàm MID được dùng để trích xuất một số lượng ký tự ở giữa chuỗi, bắt đầu từ vị trí mà bạn xác định trước. Cú pháp của hàm MID như sau:

=MID(text, start_num, num_chars)

Trong đó: 

• text là chuỗi văn bản hoặc ô dữ liệu.
• start_num là vị trí bắt đầu cắt (tính từ trái sang phải, ký tự đầu tiên có vị trí là 1).
• num_chars là số ký tự cần lấy kể từ vị trí bắt đầu.

Ví dụ, bạn có bảng dữ liệu dưới đây:

*Yêu cầu: Bạn cần lấy mã loại hàng là 2 ký tự nằm giữa chuỗi, ngay sau dấu gạch nối đầu tiên.

Công thức:

=MID(A2;4;2)

Trong đó:

• Trong chuỗi “SP-AO-001”, ký tự “S” có vị trí là 1.
• Ký tự thứ 4 là “A”, tức là ký tự đầu tiên của phần loại hàng.
• Hàm MID bắt đầu cắt từ vị trí 4 và lấy 2 ký tự, nên kết quả sẽ là “AO”.

Kết quả:

Tại sao cần kết hợp hàm COUNTIF với hàm cắt chuỗi (LEFT, MID, RIGHT)?

Trong thực tế làm việc với Excel và Google Sheets, dữ liệu hiếm khi tồn tại ở dạng đơn giản như một con số riêng lẻ hoặc một từ độc lập. Phần lớn dữ liệu được thiết kế theo cấu trúc mã hóa để phục vụ cho việc quản lý và phân loại. 

Nếu bạn chỉ sử dụng COUNTIF theo cách thông thường, bạn chỉ có thể đếm khi toàn bộ chuỗi khớp hoàn toàn với điều kiện. Tuy nhiên, trong công việc thực tế, bạn thường cần đếm riêng lẻ. Lúc này, việc kết hợp hàm COUNTIF với các hàm xử lý chuỗi như LEFT, MID hoặc RIGHT để tách đúng phần dữ liệu cần kiểm tra trước khi đếm là vô cùng cần thiết. 

Dưới đây là các tình huống cụ thể để bạn hiểu rõ vì sao việc kết hợp này là cần thiết.

• COUNTIF kết hợp hàm LEFT – Đếm theo tiền tố (ký tự đầu chuỗi)

Trong nhiều bảng dữ liệu, phần đầu của chuỗi thường đại diện cho loại thông tin quan trọng như mã sản phẩm hoặc mã phòng ban. Khi bạn muốn đếm số lượng mã bắt đầu bằng một nhóm ký tự cố định, nên sử dụng hàm COUNTIF kết hợp hàm LEFT. Hàm LEFT có nhiệm vụ cắt số ký tự bên trái của chuỗi. Sau đó, COUNTIF sẽ kiểm tra phần ký tự đã được cắt đó có thỏa điều kiện hay không. 

Ví dụ thực tế: Bạn có danh sách mã sản phẩm và muốn đếm bao nhiêu mã thuộc nhóm “SP”. Thay vì đếm toàn bộ chuỗi, bạn chỉ cần kiểm tra 2 ký tự đầu tiên.

• COUNTIF kết hợp hàm RIGHT – Đếm theo hậu tố (ký tự cuối chuỗi)

Trong một số trường hợp, phần cuối chuỗi mới là thông tin quan trọng, chẳng hạn như năm sản xuất, mã đợt phát hành, số thứ tự đơn hàng. Lúc này, bạn cần sử dụng hàm COUNTIF kết hợp hàm RIGHT. Hàm RIGHT sẽ trích xuất số ký tự từ bên phải chuỗi. Sau đó, COUNTIF sẽ so sánh phần đó với điều kiện bạn đặt ra.

Ví dụ: Bạn cần biết có bao nhiêu mã kết thúc bằng “2026”. Thay vì dùng ký tự đại diện dễ sai lệch, bạn có thể cắt chính xác 4 ký tự cuối để so sánh.

• COUNTIF kết hợp hàm MID – Đếm theo ký tự nằm giữa chuỗi

Trong nhiều hệ thống mã hóa dữ liệu, thông tin quan trọng không nằm ở đầu hoặc cuối chuỗi mà nằm ở giữa. Trong trường hợp này, bạn cần sử dụng hàm MID kết hợp RIGHT hoặc COUNTIF, tùy theo mục đích xử lý. Hàm MID cho phép bạn cắt chuỗi từ một vị trí xác định với số lượng ký tự cụ thể. Sau khi trích xuất phần giữa, bạn dùng COUNTIF để đếm số lượng mã thỏa mãn điều kiện.

Ví dụ thực tế: Sếp yêu cầu bạn đếm số đơn hàng vận chuyển bằng đường hàng không (ký hiệu “AIR” nằm giữa mã). Nếu bạn không sử dụng MID, bạn sẽ khó kiểm soát chính xác vị trí ký tự.

Cách sử dụng hàm COUNTIF kết hợp hàm  LEFT – đếm theo ký tự đầu

Trong thực tế làm việc với Excel và Google Sheets, phần đầu của chuỗi thường đại diện cho nhóm phân loại chính như mã sản phẩm, mã phòng ban hoặc loại đơn hàng. Khi bạn cần đếm số ô bắt đầu bằng một nhóm ký tự cố định, có thể dùng wildcard (*). Tuy nhiên, nếu bạn muốn kiểm soát chính xác số lượng ký tự đầu tiên, thì hàm COUNTIF kết hợp hàm LEFT sẽ là giải pháp tối ưu và chuyên nghiệp hơn.

Giả sử có bảng dữ liệu như dưới đây:

*Yêu cầu: Đếm số lượng mã có 2 ký tự đầu là “SP”.

Công thức:

=COUNTIF(ARRAYFORMULA(LEFT(A2:A5;2));”SP”)

Trong đó:

• Hàm LEFT(A2:A5,2) sẽ trích xuất 2 ký tự đầu của từng ô.
• Kết quả trả về một danh sách như: SP, SP, TP, SP.

Kết quả:

Cách sử dụng hàm COUNTIF kết hợp hàm  RIGHT – đếm theo ký tự cuối

Tương tự như hàm LEFT, khi phần thông tin quan trọng nằm ở cuối chuỗi như năm, mã chi nhánh hoặc trạng thái đơn hàng, bạn nên sử dụng hàm COUNTIF kết hợp hàm RIGHT để đảm bảo độ chính xác.

Giả sử có bảng dữ liệu như dưới đây:

*Yêu cầu: Đếm số đơn hàng có 4 ký tự cuối là “2026”.

Công thức:

=COUNTIF(ARRAYFORMULA(RIGHT(A2:A5;4));”2026″)

Trong đó:

• RIGHT(A2:A5,4) sẽ lấy 4 ký tự cuối của từng mã.
• Kết quả trả về: 2026, 2025, 2026, 2024.
• COUNTIF sẽ đếm số ô đúng bằng 2026.

Kết quả:

Cách sử dụng hàm COUNTIF kết hợp hàm MID – đếm ký tự giữa chuỗi

Trong nhiều hệ thống mã hóa, thông tin quan trọng nằm ở giữa chuỗi. Khi đó, bạn cần sử dụng hàm COUNTIF kết hợp hàm MID để kiểm soát vị trí ký tự chính xác.

Giả sử có bảng dữ liệu như dưới đây:

*Yêu cầu: Đếm số mã có khu vực là “HN”.

Công thức:

=COUNTIF(ARRAYFORMULA(MID(A2:A5;4;2));”HN”)

Trong đó:

• MID(A2:A5,4,2) lấy 2 ký tự bắt đầu từ vị trí thứ 4.
• Hàm trả về: HN, HC, HN, DN.
• COUNTIF sẽ đếm số lần xuất hiện của “HN”.

Kết quả:

Cách hàm MID kết hợp RIGHT – Tách và đếm ký tự chính xác

Đây là kỹ thuật nâng cao khi bạn cần phân tích nhiều phần khác nhau trong cùng một chuỗi. Việc hàm MID kết hợp RIGHT giúp bạn tách từng thành phần của mã và áp dụng điều kiện nhiều tầng. 

Giả sử có bảng dữ liệu như dưới đây:

*Yêu cầu: Đếm số mã thuộc khu vực HN và năm 2026.

Công thức:

=COUNTIFS(

ARRAYFORMULA(MID(A2:A5;4;2));”HN”;

ARRAYFORMULA(RIGHT(A2:A5;4));”2026″)

Trong đó:

• MID tách khu vực ở giữa chuỗi.
• RIGHT tách năm ở cuối chuỗi.
• COUNTIFS hoặc SUMPRODUCT kiểm tra đồng thời hai điều kiện.
• Chỉ những mã thỏa cả hai điều kiện mới được đếm.

Kết quả:

Lỗi và cách khắc phục khi hàm COUNTIF kết hợp hàm LEFT, MID, RIGHT

Khi bạn sử dụng hàm COUNTIF kết hợp hàm LEFT, MID, RIGHT, đôi khi sẽ gặp khó khăn trong công thức. Dưới đây là những lỗi phổ biến nhất khi lồng các hàm vào với nhau để xử lý các chuỗi. 

Lỗi #NAME?

Lỗi #NAME? thường xuất hiện khi Excel hoặc Google Sheets không nhận diện được tên hàm. Nguyên nhân phổ biến gây ra các lỗi khi kết hợp hàm bao gồm:

• Bạn viết sai tên hàm, ví dụ viết nhầm COUNTIF thành COUNTIFF hoặc SUMPRODUCT thành SUMPRODUT.
• Bạn sử dụng COUNTIFS nhưng lại nhập thiếu điều kiện.
• Bạn quên đặt chuỗi điều kiện trong dấu ngoặc kép “”.
• Bạn nhập sai dấu phân tách tham số (dấu phẩy , hoặc dấu chấm phẩy 😉 tùy theo thiết lập ngôn ngữ hệ thống.

Bạn có thể khắc phục theo các cách cơ bản sau:

• Kiểm tra lỗi chính tả của toàn bộ tên hàm.
• Kiểm tra các chuỗi điều kiện đã được đặt trong dấu ngoặc kép chưa.
•  Kiểm tra dấu phân tách tham số theo đúng thiết lập vùng miền.

Lỗi #VALUE

Lỗi #VALUE! là lỗi liên quan đến kiểu dữ liệu hoặc tham số không hợp lệ. Khi bạn dùng hàm COUNTIF kết hợp hàm LEFT, MID hoặc RIGHT, lỗi này thường xuất hiện trong các trường hợp sau:

• Tham số num_chars trong LEFT hoặc RIGHT là số âm hoặc bằng 0.
• Tham số start_num trong hàm MID nhỏ hơn 1.
• start_num vượt quá độ dài thực tế của chuỗi.
• Vùng dữ liệu chứa ô đã bị lỗi sẵn.
• Bạn dùng công thức mảng nhưng không nhập đúng cách (đối với Excel phiên bản cũ cần nhấn Ctrl + Shift + Enter).

Bạn có thể xử lý lỗi #VALUE bằng các cách sau:

• Kiểm tra lại tham số start_num và num_chars, đảm bảo các giá trị này là số nguyên dương.
• Kiểm tra xem ô dữ liệu có chứa lỗi khác như #N/A hoặc #REF! hay không.

Lỗi #ERROR!

Lỗi #ERROR! chủ yếu xuất hiện trong Google Sheets khi công thức bị sai cú pháp nghiêm trọng. Những nguyên nhân phổ biến gây ra lỗi #ERROR! bao gồm:

• Thiếu hoặc thừa dấu ngoặc đơn.
• Không đóng đủ dấu ngoặc kép.
• Quên bọc hàm LEFT, MID hoặc RIGHT bằng ARRAYFORMULA khi sử dụng cho cả một vùng dữ liệu.
• Lồng hàm sai cấu trúc.

Bạn có thể xử lý lỗi #ERROR! theo các cách thức sau:

• Kiểm tra lại toàn bộ dấu ngoặc mở và đóng. Google Sheets thường tô màu từng cặp ngoặc, bạn có thể dựa vào màu để phát hiện chỗ sai.
• Đảm bảo mỗi dấu ngoặc mở đều có một dấu ngoặc đóng tương ứng.
• Khi dùng vùng dữ liệu với hàm LEFT, MID hoặc RIGHT, hãy bọc chúng trong ARRAYFORMULA.

FAQ – Câu hỏi thường gặp

1 – Hàm COUNTIF có dùng được với nhiều điều kiện không?

Hàm COUNTIF chỉ hỗ trợ 1 điều kiện duy nhất. Nếu bạn muốn đếm nhiều điều kiện, phải chuyển sang dùng hàm COUNTIFS. Cú pháp của hàm COUNTIFS tương tự nhưng cho phép bạn thêm nhiều cặp vùng điều kiện vào sau nhau. 

2 – Hàm LEFT có áp dụng cho dữ liệu số không?

Có. Khi bạn dùng hàm LEFT để cắt một con số, kết quả trả về sẽ là chuỗi văn bản. Do đó, bạn nên cần lưu ý với các phép tính so sánh sau đó bị sai nếu bạn so sánh với con số đó. Nhưng để có thể khắc phục, bạn có thể dùng hàm như sau VALUE(LEFT(…)) để chuyển đổi kết quả về dạng số.

3 – Hàm COUNTIF có đếm được ký tự giữa không?

Có. Nhưng bạn cần phải sử dụng ký tự đại diện. Bạn hãy dùng cấu trúc “*ký-tự-giữa*” để đếm các ô chứa chuỗi đó ở bất kỳ vị trí nào, hoặc dùng ? để xác định chính xác vị trí của ký tự đó.

4 – Có cần dùng COUNTIFS khi kết hợp LEFT không?

Không. Nếu bạn có từ 2 điều kiện trở lên, hàm COUNTIFS sẽ giúp công thức dễ quản lý hơn so với việc lồng ghép hàm COUNTIF vào nhau.

Lời kết

Tóm lại, việc thành thạo hàm COUNTIF kết hợp hàm LEFT, MID, RIGHT giúp bạn nâng cao khả năng xử lý dữ liệu. Hy vọng sau bài viết này, bạn có thể tự tin áp dụng vào công việc thực tế và xây dựng các báo cáo thông minh hơn trong Excel và Google Sheets.

Ngoài ra, để biết thêm nhiều cách sử dụng các hàm cơ bản khác, bạn đừng bỏ lỡ các bài viết tiếp theo của GCS Việt Nam qua các kênh dưới đây nhé.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777