Công thức chung
=COUNTIF(range,""&high)
Tóm lược
Để đếm các giá trị ô không nằm giữa hai số, bạn có thể sử dụng hàm COUNTIF. Trong ví dụ được hiển thị, công thức trong ô K5, được sao chép xuống, là:
=COUNTIF(C5:G5,""&J5)
Tại mỗi hàng mới, công thức này trả về tổng số giá trị không nằm giữa giá trị thấp và cao trong cột I và J.
Giải trình
Mục tiêu của ví dụ này là đếm các giá trị số được ghi lại trong 5 ngày không nằm giữa hai số, giá trị thấp và giá trị cao. Nói cách khác, để đếm các giá trị "nằm ngoài phạm vi". Lưu ý rằng mỗi hàng, có nhãn AG, có giới hạn cao và thấp riêng, trong cột I và J.
Lúc đầu, bạn có thể nghĩ rằng sử dụng hàm COUNTIFS với hai tiêu chí. Tuy nhiên, vì COUNTIFS kết hợp tiêu chí với logic AND, nó không thể được sử dụng với hai tiêu chí trong trường hợp này. Logic của giá trị nhỏ hơn giá trị thấp hơn VÀ lớn hơn giá trị cao hơn sẽ luôn không thành công và kết quả sẽ luôn bằng không. Thay vào đó, chúng ta cần logic HOẶC.
Một giải pháp đơn giản là sử dụng hàm COUNTIF hai lần như sau:
=COUNTIF(C5:G5,""&J5)
COUNTIF đầu tiên đếm các giá trị dưới giá trị trong I5 và COUNTIF thứ hai đếm các giá trị trên giá trị trong J5. Khi được cộng với nhau, hai kết quả này sẽ xử lý đúng logic được yêu cầu: nhỏ hơn I5 HOẶC lớn hơn J5. Lưu ý rằng các toán tử lớn hơn (">") và nhỏ hơn ("<") được nối với các tham chiếu ô bằng một toán tử dấu và (&), một dạng hàm RACON.
Với SUMPRODUCT
Một giải pháp thanh lịch hơn một chút là sử dụng hàm SUMPRODUCT với hai biểu thức logic như sau:
=SUMPRODUCT((C5:G5J5))
Lưu ý rằng chúng ta không cần sử dụng nối với các tham chiếu ô như với COUNTIF ở trên; biểu thức tiêu chuẩn hoạt động tốt.
Đây là một ví dụ về việc sử dụng đại số boolean với phép cộng (+), tạo logic OR. Khi các biểu thức này được đánh giá, chúng ta có hai mảng giá trị TRUE và FALSE như sau:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Phép toán tự động ép các giá trị TRUE và FALSE thành 1s và 0s. Kết quả có thể được hình dung như thế này:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
Điều này dẫn đến một mảng duy nhất chứa hai số 1:
=SUMPRODUCT((0,0,1,0,1))
Chỉ với một mảng để xử lý, SUMPRODUCT tính tổng các mục trong mảng và trả về kết quả cuối cùng là 2.
