Hàm COUNTIFS, cách dùng hàm đếm ô theo nhiều điều kiện trong Excel – Kách Hay .Com đã tổng hợp thông tin từ nhiều nguồn, giúp bạn có góc nhìn đa chiều hơn. Nào chúng ta bắt đầu thôi
Hàm COUNTIFS cách dùng hàm đếm ô theo nhiều điều kiện trong Excel
Hàm COUNTIFS trong Excel có nhiều ứng dụng thực tế. Sau đây là những điều bạn cần biết về cách sử dụng hàm COUNTIFS trong Excel .
Hàm COUNTIFS trong Excel dùng để đếm các ô thỏa mãn nhiều điều kiện cho trước. Hàm COUNTIFS là một trong những hàm thống kê Excel thông dụng nhất trong Excel, là hàm nâng cao của hàm COUNTIF chỉ dùng đếm ô với một điều kiện cụ thể. Khi thực hiện xong với hàm COUNTIFS, người dùng dễ dàng tìm được ô kết quả thỏa mãn điều kiện được chỉ định trong truy vấn. Các điều kiện có thể là số, ngày tháng, văn bản hoặc ô chứa ngày tháng. Bài viết sau đây sẽ hướng dẫn bạn cách sử dụng hàm COUNTIFS trong Excel.
- Cách so sánh dữ liệu trong 2 cột Excel
- Cách dùng hàm DSUM tính tổng với điều kiện phức tạp trong Excel
- Cách dùng hàm SUMIF tính tổng có điều kiện trong Excel
- Sử dụng hàm VLOOKUP và ví dụ cụ thể
Hướng dẫn sử dụng hàm COUNTIFS trong Excel
Cú pháp của hàm COUNTIFS là =COUNTIFS(phạm vi_tiêu chí1, tiêu chí1, [phạm vi_tiêu chí2, tiêu chí2],…) .
Ở trong:
- Criteria_range1 là vùng chọn đầu tiên cần thống kê, giá trị cần thống kê.
- Tiêu chí 1 là điều kiện áp dụng cho lựa chọn “Criteria_Range1”. Giá trị bắt buộc có thể là ô, biểu thức hoặc văn bản.
- [criteria_range2,criteria2] là các cặp điều kiện và lựa chọn bổ sung, cho phép tối đa 127 cặp lựa chọn và điều kiện.
Khi sử dụng hàm COUNTIFS, hãy ghi nhớ:
- Các lựa chọn bổ sung phải có cùng số lượng hàng và cột như phạm vi Criteria_Range1 có thể được phân tách.
- Nếu điều kiện lựa chọn là một ô trống, thì hàm COUNTIFS sẽ tự động coi giá trị là 0.
- Các ký tự có thể được sử dụng? Để thay thế một ký tự cụ thể, * thay thế toàn bộ chuỗi. Nếu bạn cần tìm cái này? hoặc thực * thì gõ dấu ~ trước ký tự này.
- Lưu ý rằng hàm COUNTIFS không phân biệt chữ hoa chữ thường.
- Nói chung, giá trị văn bản nên được trích dẫn và không nên là số. Tuy nhiên, khi một toán tử logic được đặt trong một số, thì số đó và toán tử đó phải được đặt trong dấu ngoặc kép, như minh họa bên dưới:
=COUNTIFS(A1:A10,100) // đếm bằng 100 =COUNTIFS(A1:A10,”>50″) // đếm lớn hơn 50 =COUNTIFS(A1:A10,”jim”) // đếm bằng ” jim”
Lưu ý : Các điều khoản bổ sung phải tuân theo các quy tắc tương tự.
- Nếu bạn sử dụng một giá trị từ một ô khác trong một điều kiện, tham chiếu ô phải được nối với một toán tử khi được sử dụng. Trong ví dụ sau, COUNTIFS đếm các giá trị trong A1:A10 nhỏ hơn giá trị trong ô B1. Lưu ý rằng toán tử nhỏ hơn (dưới dạng văn bản) được đặt trong dấu ngoặc kép, nhưng không cần tham chiếu ô:
=COUNTIFS(A1:A10,”<“&B1) // đếm các ô nhỏ hơn B1
Lưu ý : COUNTIFS là một trong nhiều hàm chia điều kiện thành hai phần: phạm vi + tiêu chí. Điều này dẫn đến một số mâu thuẫn với các công thức và hàm khác.
- COUNTIFS có thể đếm các ô trống hoặc không trống. Các công thức sau đây đếm các ô trống và không trống trong phạm vi A1:A10:
=COUNTIFS(A1:A10,”<>”) // không để trống =COUNTIFS(A1:A10,””) // để trống
- Cách dễ nhất để sử dụng COUNTIFS với ngày là sử dụng tham chiếu ô để chỉ ngày hợp lệ trong một ô khác. Ví dụ: để đếm các ô trong A1:A10 chứa ngày lớn hơn ngày trong B1, bạn có thể sử dụng công thức như sau:
=COUNTIFS(A1:A10, “>”&B1) // đếm ngày lớn hơn A1 Ví dụ sử dụng hàm COUNTIFS trong Excel
1. Bảng dữ liệu số 1
Ta có bảng số liệu sau để thực hiện một số yêu cầu trên bảng.
Ví dụ 1: Tính số lao động nam có 25 ngày làm việc.
Tại ô cần nhập kết quả chúng ta nhập công thức =COUNTIFS(C2:C7,”Nam”,D2:D7,25) rồi nhấn Enter.
Trong đó:
- C2:C7 là vùng đếm 1 bắt buộc với Giới tính của nhân viên.
- Nam là điều kiện của vùng đếm 1.
- C2:C7 là vùng đếm 2 với Ngày công của nhân viên.
- 25 là điều kiện của vùng đếm 2.
Kết quả chúng ta có 2 nhân viên là Nam có 25 ngày công.
Ví dụ 2: Tính số nhân viên Nam có ngày nghỉ là 0.
Tại ô nhập công thức chúng ta nhập =COUNTIFS(C2:C7,”Nam”,E2:E7,0) rồi nhấn Enter.
Kết quả hiển thị 1 nhân viên Nam có 0 ngày nghỉ.
Ví dụ 3: Tính số nhân viên Nam có ngày công 25 ngày và có số ngày nghỉ ít hơn hoặc bằng 2 ngày.
Chúng ta nhập công thức =COUNTIFS(C2:C7,”Nam”,D2:D7,25,E2:E7,”<=2″) rồi nhấn Enter.
Kết quả có 2 nhân viên Nam đáp ứng đủ yêu cầu có ngày nghỉ ít hơn hoặc bằng 2 ngày.
2. Bảng dữ liệu số 2
Ví dụ 1: Tính tổng các mặt hàng Đài Loan có đơn giá bán dưới 200,000đ.
Tại ô nhập kết quả chúng ta nhập công thức tính =COUNTIFS(B2:B7,”*Đài Loan”,C2:C7,”<200,000″) rồi nhấn Enter.
Kết quả được 2 mặt hàng Đài Loan đúng với yêu cầu.
Ví dụ 2: Tính tổng sản phẩm mặt hàng Đài Loan có đơn giá trong khoảng 100,000đ đến 150,000đ.
Tại ô nhập kết quả chúng ta nhập công thức =COUNTIFS(B2:B7,”*Đài Loan”,C2:C7,”>100000″,C2:C7,”<150000″) rồi nhấn Enter.
Kết quả sẽ có 2 sản phẩm Đài Loan có giá trong khoảng 100,000đ – 150,000đ.
Cách sử dụng hàm COUNTIFS kết hợp SUM trong Excel
Hàm COUNTIFS về cơ bản là một phiên bản phức tạp hơn của hàm COUNTIF. Ưu điểm chính mà COUNTIFS vượt trội hơn so với COUNTIF là nó hỗ trợ nhiều điều kiện và phạm vi.
Tuy nhiên, bạn cũng có thể xác định một phạm vi và một điều kiện duy nhất cho hàm COUNTIFS, tương tự như đã làm với hàm COUNTIF.
Một điều quan trọng mà bạn nên hiểu về hàm COUNTIFS trước khi sử dụng là hàm này không chỉ đơn giản là tính tổng kết quả của các ô đáp ứng tiêu chí cho mỗi phạm vi ô.
Trong thực tế, nếu bạn có hai điều kiện cho hai phạm vi, các ô trong phạm vi đầu tiên được lọc hai lần: Một lần qua điều kiện đầu tiên và sau đó qua điều kiện thứ hai. Điều này có nghĩa là hàm COUTNIFS sẽ chỉ trả về các giá trị đáp ứng cả hai điều kiện, trong phạm vi đã cho của chúng.
Bạn có thể hiểu rõ hơn về chức năng của hàm COUNTIFS bằng cách nghiên cứu ví dụ bên dưới.
Ví dụ hàm COUNITFS trong Excel
Ngoài màu sắc của táo, còn có một cột mô tả kích thước của chúng. Mục tiêu cuối cùng trong ví dụ này là đếm số lượng táo đỏ cỡ lớn.
1. Chọn ô mà bạn muốn hiển thị kết quả. (Trong ví dụ này, bài viết sẽ hiển thị số lượng táo đỏ lớn trong ô E3).
2. Đi tới thanh công thức và nhập công thức bên dưới:
=COUNTIFS(A3:A11, “Đỏ”, B3:B11, “Lớn”)
3. Với lệnh này, công thức kiểm tra các ô từ A3 đến A11 cho điều kiện “Red”. Các ô vượt qua vòng kiểm tra này sau đó được kiểm tra thêm lần nữa trong phạm vi từ B3 đến B11 cho điều kiện “Big”.
4. Nhấn nút Enter.
5. Bây giờ, Excel sẽ đếm số lượng táo đỏ cỡ lớn.
Ví dụ kết quả của hàm Excel COUNTIFS
Hãy quan sát cách công thức đếm các ô có cả thuộc tính red và big. Công thức lấy các ô từ A3 đến A11 và kiểm tra chúng để tìm kết quả red. Các ô vượt qua điều kiện này, sau đó lại được kiểm tra với điều kiện tiếp theo trong phạm vi thứ hai, trong trường hợp này, là điều kiện big.
Kết luận, các phạm vi và điều kiện sau phạm vi và điều kiện đầu tiên ngày càng thu hẹp bộ lọc đếm và không độc lập với nhau. Vì vậy, kết quả cuối cùng của công thức là những quả táo có màu đỏ (red) và cỡ to (big). Bạn có thể đếm số lượng táo đỏ hoặc cỡ lớn bằng cách kết hợp hàm COUNTIF với hàm SUM.
1. Chọn ô mà bạn muốn hiển thị kết quả của công thức. (Trong ví dụ này, bài viết sẽ sử dụng ô E6).
2. Nhập công thức bên dưới:
=SUM(COUNTIF(A3:A11, “Đỏ”), COUNTIF(B3:B11, “Lớn”))
3. Công thức này sẽ đếm các ô chứa táo đỏ, sau đó là số ô chứa táo lớn, và cuối cùng, nó sẽ tính tổng hai số này.
4. Nhấn nút Enter.
5. Bây giờ Excel sẽ đếm và hiển thị số lượng táo cỡ lớn hoặc có đỏ.
Kết hợp hàm COUNTIF với hàm SUM
Trên đây là 2 bảng dữ liệu với cách sử dụng cho hàm COUNTIFS trong Excel. Người dùng phải viết điều kiện đi kèm với vùng dữ liệu điều kiện để hàm COUNTIFS xác định chính xác.
Cách sửa các lỗi thường gặp khi dùng hàm COUNTIFS trong Excel
COUNTIFS không hoạt động khi đếm các giá trị text
Khi đếm các chuỗi text, chúng phải được chèn vào bên trong dấu ngoặc kép. Nếu không, hàm COUNTIFS sẽ không thể đếm chuỗi văn bản và sẽ trả về giá trị 0. Ở ảnh ví dụ sau, chuỗi text chưa được đặt vào trong dấu ngoặc kép. Vì thế, công thức này trả kết quả bằng 0.
Để khắc phục lỗi này, bạn chỉ cần viết lại chính xác công thức: =COUNTIFS(E5:E12, “Car”)
COUNTIFS không hoạt động vì sai tham chiếu phạm vi
Khi dùng nhiều hơn một tiêu chí trong hàm COUNTIFS, phạm vi của ô cho tiêu chí khác phải có cùng số lượng ô. Nếu không, hàm COUNTIFS sẽ không hoạt động.
Giả sử ở ví dụ này muốn đếm doanh số bán ô tô ở Austin. Công thức được nhập là =COUNTIFS(E5:E12,”Car”,D5:D11,”Austin”). Quan sát kỹ công thức, bạn sẽ thấy phạm vi tiêu chí đầu tiên ở đây là E5:E12 nhưng phạm vi ở tiêu chí thứ hai là D5:D11. Số ô trong phạm vi cho tiêu chí này không giống nhau.
Giờ nhấn Enter, công thức sẽ trả về lỗi #VALUE!.
Viết lại công thức đúng như sau: =COUNTIFS(E5:E12,”Car”,D5:D12,”Austin”)
COUNTIFS (Hàm COUNTIFS)
Excel cho Microsoft 365 Excel cho Microsoft 365 dành cho máy Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel Web App Excel 2010 Xem thêm…Ít hơn
Bạn được mời dùng thử Microsoft 365 miễn phí
Mở khóa ngay bây giờ
Hàm COUNTIFS áp dụng tiêu chí cho các ô trong nhiều dải ô và đếm số lần đáp ứng tất cả các tiêu chí.
Video này là một phần trong khóa đào tạo có tên Các hàm IF nâng cao.
Cú pháp
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Cú pháp của hàm COUNTIFS có các đối số sau đây:
- criteria_range1 Bắt buộc. Phạm vi thứ nhất trong đó cần đánh giá các tiêu chí liên kết.
- criteria1 Bắt buộc. Tiêu chí dưới dạng một số, biểu thức, tham chiếu ô hoặc văn bản để xác định những ô nào cần đếm. Ví dụ: tiêu chí có thể được biểu thị là 32, “>32”, B4, “táo” hoặc “32”.
- criteria_range2, criteria2, … Tùy chọn. Những phạm vi bổ sung và tiêu chí liên kết của chúng. Cho phép tối đa 127 cặp phạm vi/tiêu chí.
Quan trọng: Mỗi phạm vi bổ sung phải có cùng số hàng và cột với đối số phạm vi tiêu chí 1. Các phạm vi không nhất thiết phải liền kề với nhau.
Chú thích
- Tiêu chí của mỗi phạm vi sẽ được áp dụng cho một ô mỗi lần. Nếu tất cả các ô thứ nhất thỏa mãn các tiêu chí liên kết của chúng, thì số đếm tăng thêm 1. Nếu tất cả các ô thứ 2 thỏa mãn các tiêu chí liên kết của chúng, thì số đếm lại tăng thêm 1, và cứ như vậy cho đến khi đã đánh giá xong tất cả các ô.
- Nếu đối số tiêu chí là tham chiếu tới một ô trống, thì hàm COUNTIFS coi ô trống là giá trị 0.
- Bạn có thể sử dụng các ký tự đại diện — dấu chấm hỏi (?) và dấu hoa thị (*) — trong tiêu chí. Dấu chấm hỏi khớp với bất kỳ ký tự đơn nào và dấu hoa thị khớp với bất kỳ chuỗi ký tự nào. Để khớp với dấu chấm hỏi hoặc dấu hoa thị thực tế, hãy nhập dấu ngã ( ~ ) trước ký tự.
Sao chép dữ liệu mẫu trong bảng sau, rồi dán vào ô A1 của trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn công thức, nhấn F2, rồi nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để hiển thị tất cả dữ liệu
Ví dụ 1:
| Dữ liệu | ||
|---|---|---|
| đầu tiên | 01.05.11 | |
| 2 | 02.05.11 | |
| 3 | 03.05.11 | |
| 4 | 04.05.11 | |
| 5 | 05.05.11 | |
| 6 | 06.05.11 | |
| Công thức | sự miêu tả | Kết quả |
| =ZÄHLENWENNS(A2:A7,”<6″,A2:A7,””>1″) | Đếm xem có bao nhiêu số trong ô A2 đến ô A7 nằm trong khoảng từ 1 đến 6 (không kể 1 và 6). | 4 |
| =ZÄHLENWENNS(A2:A7, “<5″,B2:B7,”<05.03.2011”) | Đếm xem từ ô A2 đến A7 có bao nhiêu hàng có các số nhỏ hơn 5 và có cả ngày trước 03/05/2011 trong các ô từ B2 đến B7. | 2 |
| =ZÄHLENWENNS(A2:A7, “<” & A6,B2:B7,”<” & B4) | Như đã mô tả trong ví dụ trước, nhưng với tham chiếu hàng thay vì hằng số trong tiêu chí. | 2 |
