Formula umum
=SUMPRODUCT(--(range1=criteria),range2)
Ringkasan
Untuk menjumlahkan atau subtotal kolom berdasarkan kriteria di kolom yang berdekatan, Anda bisa menggunakan rumus berdasarkan fungsi SUMPRODUCT. Pada contoh yang ditampilkan, rumus di J5 adalah:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Penjelasan
Pada intinya, formula ini menggunakan SUMPRODUCT ke multiply maka jumlah produk dari dua array: array1 dan array2 . Larik pertama, larik1 disiapkan untuk bertindak sebagai "filter" untuk mengizinkan hanya nilai yang memenuhi kriteria.
Larik1 menggunakan rentang yang dimulai pada kolom pertama yang berisi nilai yang harus lolos kriteria. "Nilai kriteria" ini berada di kolom di sebelah kiri, dan berbatasan langsung dengan, "nilai data".
Kriteria tersebut diterapkan sebagai pengujian sederhana yang membuat larik nilai TRUE dan FALSE:
--($B5:$H5=J$4)
Bit rumus ini "menguji" setiap nilai dalam larik pertama menggunakan kriteria yang disediakan, kemudian menggunakan negatif ganda (-) untuk memaksa hasil nilai TRUE dan FALSE menjadi 1 dan 0. Hasilnya terlihat seperti ini:
(1,0,0,0,1,0,1)
Perhatikan bahwa 1 sesuai dengan kolom 1,5, dan 7, yang memenuhi kriteria "A".
Untuk larik2 di dalam SUMPRODUCT, kami menggunakan rentang yang "digeser" oleh satu kolom ke kanan. Rentang ini dimulai dengan kolom pertama berisi nilai untuk dijumlahkan dan diakhiri dengan kolom terakhir yang berisi nilai untuk dijumlahkan.
Jadi, dalam rumus contoh di J5, setelah array diisi, kita memiliki:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Karena SUMPRODUCT diprogram secara khusus untuk mengabaikan kesalahan yang dihasilkan dari mengalikan nilai teks, larik terakhir terlihat seperti ini:
(1,0,0,0,1,0,1)
Satu-satunya nilai yang "bertahan" perkalian adalah yang sesuai dengan 1s di dalam larik1 . Anda dapat memikirkan logika dalam array1 yang "memfilter" nilai-nilai dalam array2 .
