Nếu bạn sử dụng mảng có kích thước biến đổi trong công thức Countifs của Google Sheets, bạn sẽ nhận được một lỗi. Điều này xảy ra vì hàm không hỗ trợ việc sử dụng như vậy.
Thường thì điều này xảy ra khi bạn chỉ định cột không phù hợp trong phạm vi tiêu chí 1 và 2 trong Countifs.
Trước hết, bạn nên xem qua cú pháp của hàm. Nó như sau.
Cú pháp: COUNTIFS(criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])
Khi bạn chỉ định nhiều phạm vi tiêu chí, tất cả các mảng/phạm vi đều phải có cùng kích thước theo chiều dọc và chiều ngang.
Điều đó có nghĩa là số hàng và số cột phải khớp hoặc giống nhau trong tất cả các phạm vi/mảng tiêu chí được chỉ định.
Hãy nhanh chóng xem qua một ví dụ để hiểu rõ hơn.
= countifs(A2:D10, “Apple”, E2:E10, “Yes”)
Ở đây có bốn đối số được sử dụng.
- criteria_range1 – A2:D10
- criterion1 – “Apple”
- criteria_range2 – E2:E10
- criterion2 – “Yes”
Như bạn có thể thấy, hai đối số mảng trên (criteria_range1 và criteria_range2) của COUNTIFS có kích thước khác nhau.
Ở ví dụ trên, tôi muốn đếm số lượng “Apple” trong phạm vi nhiều cột A2:D10 nếu các giá trị trong E2:E10 = “Yes”.
Cách xử lý sử dụng mảng có kích thước biến đổi trong Countifs của Google Sheets
Tôi có ba công thức khắc phục cho bạn thử khi bạn có các mảng có kích thước biến đổi trong Countifs gây ra lỗi #VALUE! như trên.
Tôi có thể có thêm nữa. Nhưng các tùy chọn dưới đây sẽ đủ cho bạn.
Tùy chọn 1 – Sử dụng mảng tiêu chí có kích thước biến đổi trong Countifs bằng cách tạo phạm vi tiêu chí ảo
Chúng ta có thể áp dụng các phương pháp khác nhau để sử dụng mảng có kích thước biến đổi trong Countifs của Google Sheets.
Để phù hợp logic với quy tắc Countifs, chúng ta có thể tạo một phạm vi tiêu chí ảo với số cột bằng nhau.
Chúng ta đã sử dụng phương pháp này trước đây trong SUMIF, tức là trả về Tổng của các hàng hoặc cột ma trận bằng cách sử dụng Sumif trong Google Sheets.
Tôi đang áp dụng kỹ thuật tương tự với COUNTIFS ở đây.
Công thức #1:
=countifs(A2:D10, “Apple”, ARRAYFORMULA(if(E2:E10=”Yes”, column(A2:D2)^0)), 1)
Kết quả: 9
Bên trên là tùy chọn đầu tiên để chỉ định cột không phù hợp trong phạm vi tiêu chí 1 và 2 trong Countifs.
Muốn tìm hiểu công thức COUNTIFS trên một cách chi tiết?
Giải thích công thức
- criteria_range1 – A2:D10
- criterion1 – “Apple”
- criteria_range2 – ARRAYFORMULA(if(E2:E10=”Yes”, column(A2:D2)^0))
- criterion2 – 1
Trong Countifs (Công thức #1), criteria_range2 là một công thức mảng.
Nó sẽ trả về một mảng có số cột bằng với criteria_range1.
Số hàng đã bằng nhau trong cả hai mảng, tức là trong criteria_range1 và 2.
Mảng ảo này sẽ có số 1 trong mỗi cột tương ứng với “Yes” trong E2:E10. Đó là lý do tôi đã sử dụng số 1 thay vì “Yes” trong criterion_2.
Dưới đây là hai tùy chọn khác mà bạn có thể xem xét so với công thức trên.
Tùy chọn 2 – Dựa trên Lọc và Kiểm tra logic
Mà không cần khớp mảng có kích thước biến đổi trong Countifs, chúng ta vẫn có thể đạt được kết quả mong muốn.
Bạn có thể tìm thấy dưới đây hai công thức kết hợp như vậy, và tôi đã đánh dấu công thức được khuyến nghị của mình.
Công thức #2 (Được khuyến nghị):
=countif(flatten(filter(A2:D10, E2:E10=”Yes”)), “Apple”)
Ở đây, tôi đã sử dụng Countif thay vì Countifs vì chúng ta chỉ có một cột sau khi xử lý dữ liệu bằng FILTER và FLATTEN.
Nó hoạt động như sau.
- Bộ lọc lọc các hàng trong phạm vi A2:D10 nếu E2:E10 là “Yes”.
- Chức năng Flatten biến bốn cột thành một cột.
- Countif hoàn thành phần còn lại (đếm “Apple”).
Công thức #3:
=sum(ArrayFormula(if((A2:D10=”Apple”)*(E2:E10=”Yes”)=1, 1, )))
Đây là một công thức khác để xem xét khi chúng ta có các mảng có kích thước biến đổi để chỉ định trong Countifs của Google Sheets.
Làm thế nào công thức này trả về số lượng giá trị có điều kiện từ nhiều cột không phù hợp?
Dưới đây là cách!
Kiểm tra logic, công thức được bao bọc bên trong SUM, trả về số 1 mỗi khi cả hai điều kiện khớp. Hãy xem minh hoạ phía dưới.
Hàm SUM trả về tổng của những giá trị đó.
Nếu bạn còn thắc mắc về công thức mảng mà tôi đã sử dụng trong SUM, bạn có thể tìm hiểu ở đây – Cách sử dụng IF, AND, OR trong mảng trong Google Sheets.
Đó chỉ là một cách thay thế để mã hóa kiểm tra logic AND dưới dạng mảng.
Hy vọng bạn có thể hiểu được những gợi ý trên.
Cảm ơn vì đã theo dõi. Chúc bạn vui vẻ!
- Countifs với nhiều tiêu chí trong cùng một phạm vi trong Google Sheets.
- Countif trong một mảng trong Google Sheets bằng cách sử dụng kết hợp Vlookup và Query.
- Cách sử dụng COUNTIF với UNIQUE trong Google Sheets.
- Countif | Countifs loại trừ các hàng ẩn trong Google Sheets.
- Google Sheets: Countifs với không bằng trong các phạm vi vô hạn.
- COUNTIFS trong một phạm vi thời gian trong Google Sheets [Cột Ngày và Giờ].
- COUNTIF để đếm theo tháng trong một phạm vi ngày trong Google Sheets.
- Điều kiện Không trống trong Countifs trong Google Sheets.
