Formula generik
=SUMPRODUCT(--(range1=criteria),range2)
Ringkasan
Untuk menjumlahkan atau subtotal lajur berdasarkan kriteria di lajur bersebelahan, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di J5 adalah:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Penjelasan
Pada intinya, formula ini menggunakan SUMPRODUCT untuk mengalikan dan menjumlahkan produk dari dua tatasusunan: array1 dan array2 . Array pertama, array1 disiapkan untuk bertindak sebagai "penapis" untuk membenarkan hanya nilai yang memenuhi kriteria.
Array1 menggunakan julat yang bermula pada lajur pertama yang mengandungi nilai yang mesti lulus kriteria. "Nilai kriteria" ini berada di lajur di sebelah kiri, dan berdekatan dengan "nilai data".
Kriteria tersebut diterapkan sebagai ujian sederhana yang menghasilkan susunan nilai BENAR dan SALAH:
--($B5:$H5=J$4)
Sedikit formula ini "menguji" setiap nilai dalam susunan pertama menggunakan kriteria yang diberikan, kemudian menggunakan negatif ganda (-) untuk memaksa nilai BENAR dan SALAH yang dihasilkan menjadi 1 dan 0. Hasilnya kelihatan seperti ini:
(1,0,0,0,1,0,1)
Perhatikan bahawa 1s sesuai dengan lajur 1,5, dan 7, yang memenuhi kriteria "A".
Untuk array2 di dalam SUMPRODUCT, kami menggunakan julat yang "dialihkan" oleh satu lajur ke kanan. Julat ini bermula dengan lajur pertama mengandungi nilai untuk dijumlahkan dan berakhir dengan lajur terakhir yang mengandungi nilai untuk dijumlahkan.
Jadi, dalam formula contoh di J5, setelah susunan diisi, kami mempunyai:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Oleh kerana SUMPRODUCT diprogramkan secara khusus untuk mengabaikan kesalahan yang disebabkan oleh mengalikan nilai teks, susunan terakhir kelihatan seperti ini:
(1,0,0,0,1,0,1)
Satu-satunya nilai yang "bertahan" pendaraban adalah nilai yang sesuai dengan 1s dalam array1 . Anda boleh memikirkan logik dalam array1 "menyaring" nilai dalam array2 .
