Hàm Đếm Có Điều Kiện Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

Bởi

Có thể bạn quan tâm: Hàm Thống Kê Trong Excel: Cách Sử Dụng, Ứng Dụng Và Mẹo Tối Ưu

Mở Đầu

Excel là một trong những công cụ xử lý dữ liệu mạnh mẽ và linh hoạt nhất hiện nay. Trong quá trình làm việc với bảng tính, việc đếm số lượng bản ghi thỏa mãn một hoặc nhiều tiêu chí là một nhu cầu thường gặp. Để thực hiện công việc này một cách nhanh chóng và chính xác, Excel cung cấp một loạt các hàm đếm có điều kiện như COUNTIF, COUNTIFS, SUMPRODUCT, DSUM, và các hàm mảng mới trong Office 365/Excel 2026 như FILTER kết hợp COUNTA. Bài viết này sẽ đưa bạn từ những kiến thức cơ bản nhất về hàm đếm có điều kiện, tới các kỹ thuật nâng cao, ví dụ thực tế, và mẹo tối ưu hoá hiệu năng khi làm việc với dữ liệu lớn.

Có thể bạn quan tâm: Công Thức Hàm Vlookup Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng Quan Về Các Hàm Đếm Có Điều Kiện

HàmĐịnh dạngSố tiêu chí hỗ trợĐặc điểm nổi bật
COUNTIFCOUNTIF(range, criteria)1Đếm số ô thỏa mãn một tiêu chí duy nhất.
COUNTIFSCOUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2…)N (không giới hạn)Đếm số ô thỏa mãn nhiều tiêu chí đồng thời.
SUMPRODUCTSUMPRODUCT(--(range1=criteria1), --(range2=criteria2), …)NHỗ trợ các điều kiện phức tạp, hàm mảng mà không cần nhập Ctrl+Shift+Enter.
DSUMDSUM(database, field, criteria)NĐược thiết kế cho cơ sở dữ liệu có tiêu đề cột, cho phép lọc theo bảng tiêu chí.
FILTER + COUNTACOUNTA(FILTER(range, (cond1)(cond2)…))NSử dụng hàm mảng động (Dynamic Array) trong Office 365, trả về mảng kết quả rồi đếm.
SUBTOTALSUBTOTAL(function_num, ref1, ref2…)1 (trong phạm vi đã lọc)Đếm các ô đã được lọc (visible) trong bảng hoặc danh sách.
AGGREGATEAGGREGATE(function_num, options, array, k)1Tương tự SUBTOTAL nhưng có tùy chọn bỏ qua lỗi, ẩn, vv.

1.1 Khi Nào Nên Dùng Hàm Nào?

  • COUNTIF: Khi chỉ có một tiêu chí đơn giản (ví dụ: đếm số lần xuất hiện của một giá trị cụ thể).
  • COUNTIFS: Khi cần nhiều tiêu chí đồng thời, ví dụ: đếm đơn hàng trong tháng 5 của khách hàng “A”.
  • SUMPRODUCT: Khi tiêu chí phức tạp hơn, như điều kiện không bằng, điều kiện OR, hoặc khi muốn tính tổng có điều kiện mà không muốn dùng công thức mảng.
  • DSUM: Khi dữ liệu được tổ chức thành “database” (có tiêu đề cột) và cần điều kiện linh hoạt qua bảng tiêu chí.
  • FILTER + COUNTA: Khi sử dụng Excel 365/2026, hàm này cho phép đếm nhanh sau khi lọc động, đồng thời có thể kết hợp với các hàm thống kê khác.
  • SUBTOTAL / AGGREGATE: Khi muốn đếm các ô hiển thị sau khi lọc (AutoFilter) hoặc ẩn.

Có thể bạn quan tâm: Hàm Nhân 2 Cột Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

2. Cú Pháp Cơ Bản và Ví Dụ Thực Tế

2.1 COUNTIF – Đếm Một Điều Kiện

=COUNTIF(A2:A1000, "Hà Nội")
  • A2:A1000: phạm vi cần đếm.
  • “Hà Nội”: tiêu chí, có thể là số, văn bản, hoặc ký tự đại diện (, ?).

Ví dụ thực tế:
Bạn có danh sách khách hàng trong cột B, muốn biết có bao nhiêu khách hàng ở thành phố Đà Nẵng.

=COUNTIF(B2:B500, "Đà Nẵng")

Nếu muốn đếm các giá trị bắt đầu bằng “HN”:

=COUNTIF(B2:B500, "HN")

2.2 COUNTIFS – Đếm Nhiều Điều Kiện

=COUNTIFS(A2:A1000, "Hà Nội", B2:B1000, ">500")
  • Đếm các dòng trong đó cột A = “Hà Nội” và cột B > 500.

Ví dụ thực tế:
Bạn có bảng bán hàng: Cột A là tên khách hàng, Cột B là thành phố, Cột C là doanh thu. Muốn đếm số giao dịch đạt doanh thu ≥ 1,000,000 ở Hồ Chí Minh.

=COUNTIFS(C2:C2000, ">=1000000", B2:B2000, "TP.Hồ Chí Minh")

2.3 SUMPRODUCT – Khi COUNTIFS Không Đủ

2.3.1 Đếm Điều Kiện OR

Giả sử muốn đếm số đơn hàng có trạng thái là “Đã giao” hoặc “Đang giao”.

=SUMPRODUCT(--((D2:D500="Đã giao")+(D2:D500="Đang giao")))

Giải thích:
– (D2:D500="Đã giao") trả về mảng TRUE/FALSE → -- chuyển thành 1/0.
– Dấu + hoạt động như OR (nếu một trong hai điều kiện đúng, kết quả là 1).

2.3.2 Đếm Với Điều Kiện Không Bằng

=SUMPRODUCT(--(E2:E300<>"Hủy"))

2.3.3 Đếm Với Nhiều Tiêu Chí Kết Hợp AND & OR

=SUMPRODUCT(--(A2:A1000="Hà Nội"), --((B2:B1000>500)+(C2:C1000<100)))
  • Đếm các dòng ở Hà Nội và (doanh thu > 500 hoặc chi phí < 100).

2.4 DSUM – Đếm Dựa Trên Bảng Tiêu Chí

Giả sử dữ liệu “Database” nằm trong A1:D1000, tiêu đề ở dòng 1. Bạn tạo một bảng tiêu chí ở F1:G2:

A (Tên)B (Thành phố)
1TênThành phố
2Hà Nội

Công thức:

=DSUM(A1:D1000, "Tên", F1:G2)

DSUM sẽ đếm (hoặc tổng) các giá trị trong cột “Tên” thỏa mãn tiêu chí trong bảng F1:G2. Đối với đếm, bạn có thể đặt tiêu chí “Tên” thành bất kỳ trường nào (đánh dấu ).

2.5 FILTER + COUNTA – Đếm Với Dynamic Arrays

Trong Excel 365, hàm FILTER trả về một mảng các giá trị thỏa mãn điều kiện.

=COUNTA(FILTER(A2:A1000, (B2:B1000="Hà Nội")(C2:C1000>500)))
  • FILTER lọc cột A dựa trên hai tiêu chí đồng thời.
  • COUNTA đếm số ô không rỗng trong mảng trả về.

Nếu muốn đếm các ô trống trong một phạm vi thỏa điều kiện:

=SUMPRODUCT(--(A2:A1000=""), --(B2:B1000="Hà Nội"))

3. Kỹ Thuật Nâng Cao

3.1 Đếm Với Tiêu Chí Dựa Trên Ngày Tháng

3.1.1 Đếm Các Bản Ghi Trong Tháng Này

=COUNTIFS(C2:C1000, ">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), C2:C1000, "<="&EOMONTH(TODAY(),0))
  • C2:C1000 chứa ngày giao dịch.
  • DATE(YEAR(TODAY()),MONTH(TODAY()),1) trả về ngày đầu tháng hiện tại.
  • EOMONTH(TODAY(),0) trả về ngày cuối tháng.

3.1.2 Đếm Các Bản Ghi Trong Quý Hiện Tại

=COUNTIFS(C2:C1000, ">="&DATE(YEAR(TODAY()),INT((MONTH(TODAY())-1)/3)3+1,1), C2:C1000, "<="&EOMONTH(DATE(YEAR(TODAY()),INT((MONTH(TODAY())-1)/3)3+1,1),2))

3.2 Đếm Các Giá Trị Duy Nhất Thỏa Điều Kiện

Giả sử muốn đếm số khách hàng duy nhất ở Hà Nội.

=SUMPRODUCT(1/COUNTIFS(A2:A1000, A2:A1000, B2:B1000, "Hà Nội"))
  • A2:A1000 là cột khách hàng.
  • COUNTIFS tính số lần mỗi khách hàng xuất hiện trong Hà Nội.
  • 1/ biến thành 1 cho mỗi khách hàng duy nhất, 0.5 cho hai lần, vv.
  • SUMPRODUCT cộng lại, cho ra số khách hàng duy nhất.

3.3 Đếm Với Tiêu Chí Đa Giá Trị (IN / NOT IN)

Hàm Đếm Có Điều Kiện Trong Excel
Hàm Đếm Có Điều Kiện Trong Excel

Có thể bạn quan tâm: Hàm Tách Chữ Trong Excel: Hướng Dẫn Chi Tiết, Mẹo Hay Và Ứng Dụng Thực Tế

3.3.1 Sử Dụng COUNTIFS Kết Hợp MATCH

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B500, {"Hà Nội","Đà Nẵng","TP.HCM"}, 0))))
  • Đếm các dòng mà cột B thuộc một trong ba thành phố được liệt kê.

3.3.2 NOT IN (Không Thuộc)

=SUMPRODUCT(--(ISNA(MATCH(B2:B500, {"Hà Nội","Đà Nẵng"}, 0))))

3.4 Đếm Khi Có Dữ Liệu Được Lọc (Visible Cells)

Nếu bảng dữ liệu đã được lọc (AutoFilter) và bạn muốn đếm các ô đang hiển thị, dùng:

=SUBTOTAL(103, B2:B1000) // 103 = COUNTA cho visible cells

Hoặc:

=AGGREGATE(3, 5, B2:B1000) // 3 = COUNTA, 5 = ignore hidden rows

3.5 Đếm Với Điều Kiện Dựa Trên Văn Bản (Wildcard)

  • đại diện cho bất kỳ chuỗi ký tự nào.
  • ? đại diện cho một ký tự.
=COUNTIF(A2:A1000, "học") // chứa từ "học" ở bất kỳ vị trí nào
=COUNTIF(A2:A1000, "A?C") // A bất kỳ ký tự C (ví dụ: "ABC", "AEC")

3.6 Đếm Khi Dữ Liệu Nằm Trên Nhiều Trang (Multiple Sheets)

Sử dụng hàm SUMPRODUCT kết hợp INDIRECT:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3"}&"'!A2:A1000"), "Hà Nội"))
  • INDIRECT tạo tham chiếu động tới các sheet.
  • COUNTIF áp dụng cho mỗi sheet, SUMPRODUCT cộng lại.

4. Ứng Dụng Thực Tế Trong Doanh Nghiệp

4.1 Quản Lý Bán Hàng

Nhiệm vụHàm đề xuấtLý do
Đếm số đơn hàng trên 1 triệu trong tháng hiện tạiCOUNTIFS(DoanhThu,">=1000000", Ngày,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1), Ngày,"<="&EOMONTH(TODAY(),0))Đếm nhanh, hỗ trợ nhiều tiêu chí.
Đếm số khách hàng quay lại (đơn hàng >2 lần)SUMPRODUCT(--(COUNTIF(KhachHang, KhachHang)>=2))Xác định khách hàng trung thành.
Đếm số sản phẩm còn tồn kho < mức cảnh báoCOUNTIF(TonKho,"<"&MucCanhBao)Quản lý tồn kho.

4.2 Phân Tích Nhân Sự

  • Đếm số nhân viên ở mỗi phòng ban: COUNTIFS(PhongBan, "Kế Toán").
  • Đếm nhân viên trên 30 tuổi: COUNTIF(NamSinh, "<="&YEAR(TODAY())-30).
  • Đếm nhân viên có lương > trung bình: =COUNTIF(Luong, ">"&AVERAGE(Luong)).

4.3 Kiểm Soát Chất Lượng

  • Đếm sản phẩm lỗi: =COUNTIF(TrangThai, "Lỗi").
  • Đếm sản phẩm lỗi theo dây chuyền: =COUNTIFS(TrangThai, "Lỗi", DayChuyen, "Dây chuyền 2").

5. Tối Ưu Hóa Hiệu Suất Khi Đếm Dữ Liệu Lớn

  1. Giới hạn phạm vi: Tránh dùng toàn cột (A:A) nếu không cần. Sử dụng A2:A10000 thay vì A:A.
  2. Sử dụng bảng (Table): Khi dữ liệu được định dạng thành Table (Ctrl+T), các công thức tự mở rộng và thường nhanh hơn vì Excel quản lý bộ nhớ tốt hơn.
  3. Tránh hàm mảng CSE trong các phiên bản cũ: SUMPRODUCT thường nhanh hơn {=...}.
  4. Tối ưu hàm COUNTIFS: Đặt các tiêu chí có khả năng lọc nhanh (ví dụ: ngày) trước.
  5. Sử dụng Helper Column: Khi điều kiện quá phức tạp, tạo cột phụ tính toán điều kiện (TRUE/FALSE) rồi dùng COUNTIF trên cột đó.
  6. Xóa định dạng không cần thiết: Định dạng màu, font trên hàng nghìn có thể làm chậm tính toán.
  7. Tắt tính toán tự động khi thực hiện thay đổi lớn: Formulas → Calculation Options → Manual, sau khi hoàn thành nhấn F9 để tính lại.

6. Các Lỗi Thường Gặp Khi Sử Dụng Hàm Đếm Có Điều Kiện

LỗiNguyên nhânCách khắc phục
#VALUE!Tiêu chí không hợp lệ (ví dụ: so sánh số với văn bản)Kiểm tra kiểu dữ liệu, dùng TEXT hoặc VALUE để chuyển đổi.
#N/AKhi dùng MATCH trong SUMPRODUCT và không tìm thấy giá trịThêm IFERROR(...,0) hoặc dùng ISNA.
Đếm sai vì dòng trốngCOUNTIF không tính ô trống, nhưng COUNTIFS có thể nếu tiêu chí là ""Đảm bảo tiêu chí phù hợp, hoặc dùng COUNTA cho ô không rỗng.
Kết quả không cập nhật khi dữ liệu thay đổiSử dụng hàm mảng CSE trong phiên bản cũ mà không nhấn Ctrl+Shift+Enter lạiNhấn Ctrl+Shift+Enter hoặc chuyển sang SUMPRODUCT.
Hiệu suất chậmDùng toàn cột (A:A) trên file > 100k dòngGiảm phạm vi, dùng Table, hoặc Helper Column.

7. Thực Hành: Bài Tập Tự Làm

Bài 1: Trong bảng Bán Hàng (cột A: Mã đơn, B: Khách hàng, C: Thành phố, D: Doanh thu, E: Ngày), viết công thức đếm số đơn hàng đạt doanh thu ≥ 2,000,000 và được thực hiện ở Hà Nội trong quý 2 năm 2026.
Gợi ý: Kết hợp COUNTIFS với DATE, EOMONTH.

Bài 2: Đếm số khách hàng duy nhất mua hàng ở TP.HCM và đã mua hơn 3 lần.
Gợi ý: Dùng UNIQUE (Office 365) hoặc SUMPRODUCT kết hợp COUNTIFS.

Bài 3: Bạn có 3 sheet Jan, Feb, Mar chứa cùng cấu trúc cột A: Mã SP, B: Số lượng bán. Đếm tổng số sản phẩm bán được trong 3 tháng.
Gợi ý: SUMPRODUCT(COUNTIF(INDIRECT(...))).

Bài 4: Sử dụng FILTER để lấy danh sách đơn hàng có doanh thu > 500,000 và không phải từ Đà Nẵng, sau đó đếm số dòng.
Gợi ý: COUNTA(FILTER(..., (C2:C1000<>"Đà Nẵng")(D2:D1000>500000))).

Thực hành các bài tập trên sẽ giúp bạn nắm vững cách áp dụng hàm đếm có điều kiện trong các tình huống thực tế.

8. Kết Luận

Hàm đếm có điều kiện trong Excel là công cụ thiết yếu cho mọi người làm việc với dữ liệu, từ nhân viên văn phòng tới nhà phân tích dữ liệu chuyên sâu. Bằng việc hiểu rõ cú pháp, ưu nhược điểm của từng hàm (COUNTIF, COUNTIFS, SUMPRODUCT, DSUM, FILTER+COUNTA, SUBTOTAL, AGGREGATE) và biết cách kết hợp chúng với các kỹ thuật như wildcard, dynamic arrays, helper columns, bạn có thể:

  • Giải quyết nhanh các yêu cầu báo cáo thường ngày.
  • Tối ưu hiệu năng khi làm việc với dữ liệu lớn.
  • Mở rộng sang các phân tích nâng cao như đếm khách hàng duy nhất, đếm theo tháng/quý, và đếm với nhiều tiêu chí phức tạp.

Hãy bắt đầu áp dụng ngay các công thức trên vào bảng tính của bạn, và không ngừng khám phá thêm các hàm mới khi Excel được cập nhật. Khi bạn đã thành thạo, việc trích xuất insight từ dữ liệu sẽ trở nên nhanh chóng, chính xác và ít tốn công hơn bao giờ hết. Chúc bạn thành công!

⚠️
LƯU Ý QUAN TRỌNG: Miễn trừ trách nhiệm: Mặc dù chúng tôi luôn nỗ lực để đảm bảo tính chính xác và cập nhật của thông tin, nhưng các quy định về giáo dục và tuyển sinh có thể thay đổi theo thời gian. BigSchool không chịu trách nhiệm về bất kỳ sai sót nào hoặc kết quả phát sinh từ việc sử dụng thông tin trên trang này. Độc giả nên liên hệ trực tiếp với nhà trường hoặc cơ quan chức năng để có thông tin mới nhất. Ngoài ra, BigSchool tuyệt đối không bán sản phẩm, khóa học hay cung cấp dịch vụ được nhắc đến trong bài.

Viết một bình luận