Formula umum
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Ringkasan
Untuk menjumlahkan nilai dalam kolom dan baris yang cocok, Anda dapat menggunakan fungsi SUMPRODUCT. Pada contoh yang ditampilkan, rumus di J6 adalah:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
di mana data (C5: G14), hari (B5: B14), dan kode (C4: G4) diberi nama rentang.
Penjelasan
Fungsi SUMPRODUCT dapat menangani array secara native, tanpa memerlukan kontrol shift enter.
Dalam kasus ini, kami mengalikan semua nilai dalam data rentang bernama dengan dua ekspresi yang memfilter nilai yang tidak menarik. Ekspresi pertama menerapkan filter berdasarkan kode:
(codes=J4)
Karena J4 berisi "A002", ekspresi tersebut membuat larik nilai TRUE FALSE seperti ini:
(FALSE,TRUE,FALSE,FALSE,FALSE)
Ekspresi kedua memfilter pada hari:
(days=J5)
Karena J4 berisi "Wed", ekspresi tersebut membuat larik nilai TRUE FALSE seperti ini:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Di Excel, nilai TRUE FALSE secara otomatis dipaksa menjadi nilai 1 dan 0 oleh operasi matematika apa pun, sehingga operasi perkalian memaksa array di atas menjadi satu dan nol, dan membuat array 2D dengan dimensi yang sama dengan data asli. Prosesnya dapat divisualisasikan seperti gambar di bawah ini:
Terakhir, SUMPRODUCT mengembalikan jumlah semua elemen dalam larik terakhir, 9.
Hitung, bukan jumlah
Jika Anda ingin menghitung nilai yang cocok daripada menjumlahkan, Anda dapat mempersingkat rumus menjadi:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Perhatikan hitungan ini akan mencakup sel kosong.
Catatan
- Meskipun contoh hanya memperlihatkan satu kolom yang cocok, rumus ini akan menjumlahkan beberapa kolom yang cocok dengan benar.
- Jika Anda hanya perlu mencocokkan kolom (bukan baris), Anda dapat menggunakan rumus seperti ini.
- Untuk hanya mencocokkan baris, Anda dapat menggunakan fungsi COUNTIFS.
