Hướng dẫn chi tiết về cách áp dụng hàm COUNTIF và COUNTIFS với nhiều tiêu chí trong Excel

Bài viết dưới đây do Siêu Marketing soạn ra chia sẻ chiến thuật sử dụng Hàm COUNTIF cũng như Hàm COUNTIFS trong Excel được xây dựng dựa trên nguyên tắc logic ANDOR. Các ví dụ minh họa vô cùng đa dạng bao gồm dữ liệu số, ngày tháng, văn bản và các ô có chứa dữ liệu khác nhau.

Độc giả có thể tham khảo thêm:

  • Excel và hàm Count
  • Công thức CountA trong Excel
  • Phương pháp tự đánh số nhanh qua hàm CountA
  • Khoá học tự chủ Excel

Giới thiệu về hàm COUNTIFS – cấu trúc và phương pháp sử dụng

Dưới đây là cú pháp của COUNTIFS:

=COUNTIFS(vùng_tiêu_chí1, tiêu_chí1, [vùng_tiêu_chí2, tiêu_chí2]…)

vùng_tiêu_chí1 – phạm vi được chọn để áp dụng tiêu chí số một (tiêu_chí1), yếu tố bắt buộc.

tiêu_chí1 – đây là điều kiện cụ thể cho phép áp dụng cho các giá trị như số, ô tham chiếu, chuỗi văn bản, mảng hay hàm khác của Excel, yếu tố bắt buộc. Ví dụ tiêu chí có thể là 10, “<=32”, A6, “sweets”.

[vùng_tiêu_chí2, tiêu_chí2]… – những điều kiện thêm vào, không bắt buộc. Trong một công thức, có thể nhập đến 127 cặp tiêu chí-pair.

Không cần nhớ chính xác cách viết của hàm COUNTIFS, Excel sẽ hiển thị tự động các đối số cần thiết ngay khi bạn gõ. Đối số nào đang được nhập sẽ nổi bật lên.

Cấu trúc hàm COUNTIFS trong Excel

Những lưu ý quan trọng khi sử dụng hàm COUNTIFS:

  1. Hàm COUNTIFS có khả năng đếm ô trong phạm vi dữ liệu xác định với một điều kiện hoặc nhiều phạm vi với nhiều điều kiện. Trường hợp nhiều điều kiện, chỉ đếm ô thỏa mãn mọi điều kiện được đề ra.
  2. Mỗi phạm vi dữ liệu này cần có cùng kích thước hàng và cột như phạm vi đầu tiên (đối số vùng_tiêu_chí1).
  3. Có thể tính toán các phạm vi dữ liệu gần kề hoặc không gần kề nhau.
  4. Đối với tiêu chí tham chiếu tới một ô không có dữ liệu, hàm COUNTIFS sẽ coi đó là giá trị không (0).
  5. Sử dụng các ký tự đại diện như dấu sao (*) và dấu chấm hỏi (?) trong tiêu chí cũng được chấp nhận.

Phương pháp sử dụng hàm COUNTIFS và COUNTIF với nhiều tiêu chí:

Tiến hành đếm ô với nhiều tiêu chí (logic AND):

Công thức 1: COUNTIFS với nhiều tiêu chí khác biệt

Với danh sách các sản phẩm trong hình minh họa sau, bạn muốn biết có bao nhiêu mục tồn kho (giá trị tại cột B lớn hơn 0) đồng thời chưa được bán (giá trị cột C là 0).

Đối với nhiệm vụ này, công thức bạn nên dùng là:

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

Chúng ta sẽ nhận được Kết quả là 2, bao gồm (“Cherries”“Lemons”):

Phương pháp đếm ô dựa trên logic AND với nhiều tiêu chí

Công thức 2: COUNTIFS với các tiêu chí giống hệt nhau

Nếu như bạn cần đếm những mục nào mà có cả hai cột B và C đều chứa giá trị 0, bạn vẫn cần phải nhập từng cặp vùng_tiêu_chí / tiêu_chí riêng lẻ.

Ví dụ, này là công thức chính xác để thiến hành đếm các mục có chứa số 0 trong cả cột B lẫn cột C:

Công thức COUNTIFS dưới đây cho kết quả là 1 khi chỉ mục “Grapes” trong cả hai cột đều có giá trị là “0”.

Nếu sử dụng công thức đơn giản hơn như COUNTIFS(B2: C7, “= 0”), bạn sẽ nhận được số lượng ô từ B2 đến C7 chứa giá trị 0, thay vì 1 như công thức trước, ở đây số lượng là 4.

COUNTIFS formula with identical criteria

Phương pháp đếm số ô theo nhiều tiêu chí sử dụng logic OR:

Tính năng đếm ô phù hợp với tất cả tiêu chí xác định đã được thể hiện ổn định qua ví dụ trên với sự trợ giúp của hàm COUNTIFS.

Để đếm số ô đáp ứng ít nhất một tiêu chí đặt ra tức dùng logic OR, có hai phương pháp bạn có thể áp dụng – thông qua việc kết hợp nhiều hàm COUNTIF hoặc ứng dụng công thức COUNTIFS với mảng hằng số.

Phương pháp 1: Kết hợp nhiều hàm COUNTIF hoặc COUNTIFS

Đối với bảng ở dưới, nếu bạn cần tính số lượng đơn hàng có trạng thái là “Cancelled” hoặc “Pending“, chỉ cần sử dụng hai hàm COUNTIF thông thường và bạn sẽ có kết quả như ý:

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

Counting cells that meet any of the specified criteria

Khi mỗi hàm bao gồm nhiều tiêu chí, hãy dùng COUNTIFS thay cho COUNTIF. Dưới đây là ví dụ để tính số đơn hàng “Cancelled” và “Pending” đối với “Apples”:

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

Another formula to count cells with multiple criteria and OR logic

Phương pháp 2: Sử dụng công thức SUM COUNTIFS với mảng hằng số

Khi muốn áp dụng nhiều tiêu chí, phương pháp trên sẽ khiến công thức trở nên rườm rà. Một lựa chọn khác gọn gàng hơn là liệt kê các tiêu chí vào một mảng và dùng trong criteria của hàm COUNTIFS, sau đó dùng hàm SUM để tính tổng như sau:

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

Để đếm số lượng đơn hàng có trạng thái là “Cancelled“, “Pending” hoặc “In transit” trong bảng mẫu của chúng ta, bạn sẽ sử dụng công thức:

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

A more compact formula to count cells with multiple criteria and OR logic

Đối với việc đếm dựa trên nhiều cặp criteria_range/criteria, ví dụ như đếm số đơn hàng “Apples” ở các trạng thái “Cancelled”, “Pending”, “In transit”, bạn có thể dùng công thức:

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

Counting cells with multiple criteria_range / criteria pairs and OR logic

Hướng dẫn đếm số lượng trong phạm vi xác định:

Công thức 1: Hàm COUNTIFS để tính số ô nằm trong một dải số

Nếu bạn cần đánh giá số lượng số từ 5 đến 10, không kể cả 5 và 10, định nghĩa trong phạm vi từ C2 đến C10, hãy áp dụng công thức:

=COUNTIFS(C2:C10,”>5″,C2:C10,”<10″)

Để tính cả hai số 5 và 10, hãy dùng điều kiện “lớn hơn hoặc bằng” và “nhỏ hơn hoặc bằng” như sau:

=COUNTIFS(B2:B10,”>=5″,B2:B10,”<=10″)

A COUNTIFS formula to count numbers between X and Y

Công thức 2: Sử dụng COUNTIF để tính số lượng trong khoảng từ X đến Y

Công thức COUNTIF kết hợp với một hàm khác cũng có thể được dùng để đạt được mục đích tương tự. Công thức thứ nhất tính số lượng số lớn hơn một giá trị nhất định, ví dụ 5, còn công thức thứ hai trừ đi số lượng số vượt quá giá trị cao (ví dụ là 10). Số lượng số bạn muốn tính sẽ nằm giữa hai giá trị này.

=COUNTIF(C2:C10,”>5″)-COUNTIF(C2:C10,”>10″) – các số lớn hơn 5 và nhỏ hơn 10 được đếm trong dãy C2:C10.

=COUNTIF(C2:C10,”>=5″)-COUNTIF(C2:C10,”>10″) – đếm số lượng từ 5 đến 10, bao gồm cả 5 và 10, trong phạm vi C2:C10.

Áp dụng tham chiếu ô trong công thức COUNTIFS

Khi sử dụng phép toán so sánh như “>”, “<“, “<=” hoặc “>=” cùng với tham chiếu ô trong hàm COUNTIFS, đặt các phép toán trong dấu ngoặc kép và sử dụng ký tự nối (&) trước tham chiếu ô để tạo chuỗi.

Trong ví dụ dữ liệu dưới đây, bạn cần tính số lượng đơn hàng của “Apples” với giá trên $200. Sử dụng criteria_range1 tại A2: A11 và criteria_range2 tại B2: B11, bạn có thể dùng công thức:

=COUNTIFS($A$2:$A$11,”Apples”,$B$2:$B$11,”>200″)

Bạn cũng có thể nhập tiêu chí vào các ô cụ thể ví dụ như F1 và F2 rồi tham chiếu chúng trong công thức:

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

Hãy chú ý sử dụng giá trị tuyệt đối trong criteria_rangecriteria để đảm bảo công thức không bị sai khi được sao chép sang các ô khác.

Using cell references in COUNTIFS formulas

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

Các ký tự đặc biệt sau có thể được dùng trong công thức Excel:

Ký tự hỏi (?) – thay thế cho bất kỳ ký tự nào, sử dụng để tìm số ô bắt đầu hoặc kết thúc với một số ký tự cụ thể.

Ký tự sao (*) – thay thế cho bất kỳ chuỗi ký tự nào, áp dụng cho việc đếm các ô chứa một từ hoặc chuỗi ký tự cụ thể.

Lưu ý: Nếu bạn cần đếm các ô chứa ? hoặc *, bạn cần đặt dấu ngã (~) ngay trước ký tự đó.

Xét trường hợp bạn cần đếm số dự án đã ký trong danh sách cột A. Bạn muốn biết có bao nhiêu dự án đã ký, tức là cột B nhập chữ ký người dùng. Bạn cần thêm một tiêu chí khác – trong cột D là End date.

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

Chú ý rằng, bạn không thể sử dụng ký tự đại diện cho tiêu chí thứ hai vì cột D chứa ngày tháng, không phải văn bản. Do đó, bạn sử dụng dấu phủ định để chỉ dữ liệu không rỗng.

“<>”&”” sẽ giúp bạn tìm kiếm những ô có chứa thông tin.

Công thức COUNTIFS để đếm số lượng mục nhập bất kỳ trong một cột và ô không trống ở cột khác.

Sử dụng COUNTIFS và COUNTIF với các điều kiện ngày tháng đa dạng:

Ví dụ 1. Tính số lượng ngày thuộc khoảng thời gian nhất định

Bạn có thể dùng công thức COUNTIFS kết hợp hai điều kiện hoặc ghép hai hàm COUNTIF để đếm các ngày nằm trong khoảng thời gian xác định.

Chẳng hạn, công thức dưới đây tính số lượng ngày trong khoảng từ 1/6/2014 đến 7/7/2014, áp dụng cho các ô từ C2 đến C10:

=COUNTIFS(C2:C9,”=6/1/2014″,C2:C9,”<=6/7/2014″)

=COUNTIF(C2:C9,”=6/1/2014″)-COUNTIF(C2:C9,”6/7/2014″)

Công thức COUNTIF với 2 điều kiện để đếm ngày trong một khoảng thời gian cụ thể

Ví dụ 2. Tính số lượng ngày theo nhiều tiêu chí

Một cách tương tự, công thức COUNTIFS được áp dụng để đếm những ngày phù hợp với ít nhất hai tiêu chí trong các cột khác nhau. Công thức sau đây cho bạn biết số lượng sản phẩm được mua từ sau ngày 20 tháng 5 và được giao từ sau ngày 01 tháng 6:

=COUNTIFS(C2:C9,”>5/1/2014″,D2:D9,”6/7/2014″)

Công thức COUNTIF để đếm ngày dựa theo nhiều điều kiện khác nhau

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

Sự kết hợp giữa hàm TODAYCOUNTIF cho phép bạn đếm số ngày nhìn từ ngày hôm nay.

Ví dụ, công thức COUNTIF dưới đây với hai điều kiện sẽ chỉ ra số lượng sản phẩm được mua nhưng chưa được giao hàng tính đến thời điểm hiện tại:

=COUNTIFS(C2:C9,”<“&TODAY(),D2:D9,”>”&TODAY())

Công thức COUNTIF đếm ngày dựa trên nhiều tiêu chí căn cứ vào ngày hiện tại

Công thức này có thể được điều chỉnh để phản ánh các tình huống cụ thể hơn. Ví dụ: để tính số lượng sản phẩm đã mua từ trên một tuần trước và chưa được giao:

=COUNTIFS(C2:C9,”<=”&TODAY()-7,D2:D9,”>”&TODAY())

Qua bài viết này, blog.hocexecl.online mong muốn đã cung cấp và hướng dẫn đầy đủ cho quý bạn đọc cách sử dụng cụ thể nhất công thức CountIFS và CountIF trong Excel. Nếu có bất kỳ thắc mắc nào, xin hãy để lại bình luận phía dưới để chúng tôi kịp thời giải đáp.

Khóa học Excel cơ bản đến nâng cao của Siêu Marketing chia sẻ kiến thức toàn diện và có hệ thống về các hàm và công cụ trong Excel, ứng dụng cho công việc thực tiễn. Ưu đãi đang được cung cấp cho những ai đăng ký tham gia. Hãy thực hiện đăng ký ngay tại Siêu Marketing.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop