Formula umum
=COUNTIF(range,""&high)
Ringkasan
Untuk menghitung nilai sel yang tidak berada di antara dua angka, Anda bisa menggunakan fungsi COUNTIF. Dalam contoh yang diperlihatkan, rumus di sel K5, disalin ke bawah, adalah:
=COUNTIF(C5:G5,""&J5)
Di setiap baris baru, rumus ini mengembalikan jumlah nilai bukan antara nilai rendah dan tinggi di kolom I dan J.
Penjelasan
Tujuan dari contoh ini adalah untuk menghitung nilai angka yang tercatat selama 5 hari yang tidak termasuk di antara dua angka, nilai rendah, dan nilai tinggi. Dengan kata lain, untuk menghitung nilai yang "di luar jangkauan". Perhatikan bahwa setiap baris, berlabel AG, memiliki batas bawah dan tinggi, di kolom I dan J.
Anda mungkin pada awalnya berpikir untuk menggunakan fungsi COUNTIFS dengan dua kriteria. Namun, karena COUNTIFS menggabungkan kriteria dengan logika AND, kriteria ini tidak dapat digunakan dengan dua kriteria dalam skenario ini. Logika yang nilainya kurang dari lebih rendah AND lebih besar dari nilai yang lebih tinggi akan selalu gagal dan hasilnya akan selalu nol. Sebaliknya, kita membutuhkan logika OR.
Salah satu solusi langsung adalah menggunakan fungsi COUNTIF dua kali seperti ini:
=COUNTIF(C5:G5,""&J5)
COUNTIF pertama menghitung nilai di bawah nilai di I5, dan COUNTIF kedua menghitung nilai di atas nilai di J5. Ketika ditambahkan bersama, kedua hasil ini dengan benar menangani logika yang diperlukan: kurang dari I5 ATAU lebih besar dari J5. Perhatikan operator lebih besar dari (">") dan kurang dari ("<") digabungkan ke referensi sel dengan operator ampersand (&), sebuah quirk dari fungsi RACON.
Dengan SUMPRODUCT
Solusi yang sedikit lebih elegan adalah menggunakan fungsi SUMPRODUCT dengan dua ekspresi logis seperti ini:
=SUMPRODUCT((C5:G5J5))
Perhatikan bahwa kita tidak perlu menggunakan penggabungan dengan referensi sel seperti pada COUNTIF di atas; ekspresi standar berfungsi dengan baik.
Ini adalah contoh penggunaan aljabar boolean dengan penambahan (+), yang menghasilkan logika OR. Saat ekspresi ini dievaluasi, kami memiliki dua array nilai TRUE dan FALSE seperti ini:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,TRUE)+(FALSE,FALSE,TRUE,FALSE,FALSE))
Operasi matematika secara otomatis memaksa nilai TRUE dan FALSE menjadi 1 dan 0. Hasilnya bisa divisualisasikan seperti ini:
=SUMPRODUCT((0,0,0,0,1)+(0,0,1,0,0))
Ini menghasilkan satu larik yang berisi dua 1s:
=SUMPRODUCT((0,0,1,0,1))
Dengan hanya satu larik untuk diproses, SUMPRODUCT menjumlahkan item dalam larik dan mengembalikan hasil akhir dari 2.
