Formula generik
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Ringkasan
Untuk menjumlahkan nilai dalam lajur dan baris yang sepadan, anda boleh menggunakan fungsi SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di J6 adalah:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
di mana data (C5: G14), hari (B5: B14), dan kod (C4: G4) dinamakan julat.
Penjelasan
Fungsi SUMPRODUCT dapat menangani array secara semula jadi, tanpa memerlukan shift shift enter.
Dalam kes ini, kita mengalikan semua nilai dalam data julat bernama dengan dua ungkapan yang menyaring nilai yang tidak menarik. Ungkapan pertama menggunakan penapis berdasarkan kod:
(codes=J4)
Oleh kerana J4 mengandungi "A002", ekspresi membuat susunan nilai TRUE FALSE seperti ini:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Ungkapan kedua menapis pada hari:
(days=J5)
Oleh kerana J4 mengandungi "Wed", ungkapan membuat susunan nilai TRUE FALSE seperti ini:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Di Excel, nilai TRUE FALSE secara automatik dipaksakan ke nilai 1 dan 0 oleh sebarang operasi matematik, jadi operasi pendaraban menyusun tatasusunan di atas menjadi satu dan sifar, dan membuat array 2D dengan dimensi yang sama dengan data asal. Prosesnya dapat dilihat seperti yang ditunjukkan di bawah:
Akhirnya, SUMPRODUCT mengembalikan jumlah semua elemen dalam larik terakhir, 9.
Kira dan bukannya jumlah
Sekiranya anda ingin mengira nilai yang sepadan dan bukannya menjumlahkan, anda boleh memendekkan formula menjadi:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Perhatikan jumlah ini akan merangkumi sel kosong.
Catatan
- Walaupun contoh menunjukkan hanya satu lajur yang dipadankan, formula ini akan menjumlahkan banyak lajur yang dipadankan dengan betul.
- Sekiranya anda hanya perlu memadankan lajur (bukan baris) anda boleh menggunakan formula seperti ini.
- Untuk memadankan baris sahaja, anda boleh menggunakan fungsi COUNTIFS.
