Formula umum
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Ringkasan
Untuk menghitung baris yang berisi nilai tertentu, Anda bisa menggunakan rumus array yang didasarkan pada fungsi MMULT, TRANSPOSE, COLUMN, dan SUM. Pada contoh yang ditunjukkan, rumus di G5 adalah:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
dimana datanya adalah rentang bernama B4: B12.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control shift enter.
Penjelasan
Bekerja dari dalam ke luar, kriteria logis yang digunakan dalam rumus ini adalah:
--(data=90)
dimana datanya adalah rentang bernama B4: D12. Ini menghasilkan hasil TRUE / FALSE untuk setiap nilai dalam data, dan negatif ganda memaksa nilai TRUE FALSE menjadi 1 dan 0 untuk menghasilkan array seperti ini:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Seperti data asli, larik ini berukuran 9 baris kali 3 kolom (9 x 3) dan masuk ke fungsi MMULT sebagai larik1 .
Array2 diturunkan dengan:
TRANSPOSE(COLUMN(data))
Ini adalah bagian yang rumit dan menyenangkan dari formula ini. Fungsi COLUMN digunakan hanya untuk kenyamanan sebagai cara untuk menghasilkan larik numerik dengan ukuran yang tepat. Untuk melakukan perkalian matriks dengan MMULT, jumlah kolom pada array1 (3) harus sama dengan jumlah baris pada array2 .
COLUMN mengembalikan larik 3 kolom (2,3,4), dan TRANSPOSE mengubah larik ini menjadi larik 3 baris (2; 3; 4). MMULT kemudian menjalankan dan mengembalikan hasil array 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Kami memeriksa entri bukan nol dengan> 0 dan sekali lagi memaksa TRUE FALSE ke 1 dan 0 dengan negatif ganda untuk mendapatkan array terakhir di dalam SUM:
=SUM((1;0;1;1;0;0;0;0;1))
Dalam larik terakhir ini, angka 1 mewakili baris di mana pengujian logika (data = 90) mengembalikan nilai benar. Total yang dikembalikan oleh SUM adalah hitungan dari semua baris yang berisi angka 90.
Literal mengandung
Jika Anda perlu memeriksa nilai teks tertentu, dengan kata lain, secara harfiah memeriksa untuk melihat apakah sel berisi nilai teks tertentu, Anda dapat mengubah logika dalam rumus di halaman ini untuk menggunakan fungsi ISNUMBER dan SEARCH. Misalnya, untuk menghitung sel / baris yang mengandung "apel", Anda dapat menggunakan:
=ISNUMBER(SEARCH("apple",data))
Detail tentang cara kerja rumus ini di sini.
