Formula umum
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Ringkasan
Untuk menghitung baris yang terlihat hanya dengan kriteria, Anda dapat menggunakan rumus yang agak rumit berdasarkan SUMPRODUCT, SUBTOTAL, dan OFFSET. Dalam contoh yang ditunjukkan, rumus di C12 adalah:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Kata pengantar
Fungsi SUBTOTAL dapat dengan mudah menghasilkan jumlah dan jumlah untuk baris tersembunyi dan tidak tersembunyi. Namun, itu tidak dapat menangani kriteria seperti COUNTIF atau SUMIF tanpa bantuan. Salah satu solusinya adalah menggunakan SUMPRODUCT untuk menerapkan fungsi SUBTOTAL (melalui OFFSET) dan kriteria. Detail dari pendekatan ini dijelaskan di bawah.
Penjelasan
Pada intinya, rumus ini bekerja dengan menyiapkan dua array di dalam SUMPRODUCT. Larik pertama menerapkan kriteria, dan larik kedua menangani visibilitas:
=SUMPRODUCT(criteria*visibility)
Kriteria diterapkan dengan bagian dari rumus:
=(C5:C8=C10)
Yang menghasilkan array seperti ini:
(FALSE;TRUE;FALSE;TRUE)
Dimana TRUE berarti "memenuhi kriteria". Catatan karena kita menggunakan perkalian (*) pada larik ini, nilai TRUE FALSE secara otomatis akan diubah menjadi 1 dan 0 dengan operasi matematika, jadi kita berakhir dengan:
(0;1;0;1)
Filter visibilitas diterapkan menggunakan SUBTOTAL, dengan nomor fungsi 103.
SUBTOTAL dapat mengecualikan baris tersembunyi saat menjalankan penghitungan, jadi kami dapat menggunakannya dalam kasus ini untuk membuat "filter" untuk mengecualikan baris tersembunyi di dalam SUMPRODUCT. Masalahnya adalah bahwa SUBTOTAL mengembalikan satu angka, sementara kita membutuhkan larik hasil untuk menggunakannya dengan sukses di dalam SUMPRODUCT. Triknya adalah dengan menggunakan OFFSET untuk memberi makan SUBTOTAL satu referensi per baris, sehingga OFFSET akan mengembalikan satu hasil per baris.
Tentu saja, itu membutuhkan trik lain, yaitu memberikan OFFSET sebuah array yang berisi satu angka per baris, dimulai dengan nol. Kami melakukannya dengan ekspresi yang dibangun di atas fungsi ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
yang akan menghasilkan array seperti ini:
(0;1;2;3)
Singkatnya, array kedua (yang menangani visibilitas menggunakan SUBTOTAL), dihasilkan seperti ini:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
Dan akhirnya, kami memiliki:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Yang mengembalikan 1.
Berbagai kriteria
Anda dapat memperluas rumus untuk menangani beberapa kriteria seperti ini:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Hasil penjumlahan
Untuk mengembalikan jumlah nilai dan bukan hitungan, Anda bisa menyesuaikan rumus untuk menyertakan rentang jumlah:
=SUMPRODUCT(criteria*visibility*sumrange)
Larik kriteria dan visibilitas bekerja sama seperti yang dijelaskan di atas, tidak termasuk sel yang tidak terlihat. Jika Anda membutuhkan pencocokan parsial, Anda dapat membuat ekspresi menggunakan ISNUMBER + SEARCH, seperti yang dijelaskan di sini.
