Formula umum
=SUMPRODUCT(SUMIF(codes,lookups,values))
Ringkasan
Untuk menjumlahkan nilai yang diambil dengan operasi pencarian, Anda bisa menggunakan SUMPRODUCT dengan fungsi SUMIF.
Pada contoh yang ditampilkan, rumus di H5 adalah:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Dimana kode adalah rentang bernama J4: J5 dan nilai adalah rentang bernama K4: K5.
Konteks
Terkadang Anda mungkin ingin menjumlahkan beberapa nilai yang diambil dengan operasi pencarian. Dalam contoh ini, kami ingin menjumlahkan waktu liburan yang diambil setiap minggu berdasarkan sistem kode, di mana F = sehari penuh, dan H = setengah hari. Jika suatu hari kosong, tidak ada waktu yang dihabiskan.
Tantangannya adalah menemukan rumus yang mencari dan menjumlahkan nilai yang terkait dengan F dan H.
Penjelasan
Inti dari rumus ini adalah SUMIF, yang digunakan untuk mencari nilai yang benar untuk F dan H. Menggunakan SUMIF untuk mencari nilai adalah teknik yang lebih maju yang bekerja dengan baik saat nilainya berupa numerik, dan tidak ada duplikat di "tabel pencarian ".
Trik dalam kasus ini adalah bahwa kriteria untuk SUMIF bukanlah nilai tunggal, melainkan array nilai dalam rentang C5: G5:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Karena kami memberikan SUMIF lebih dari satu kriteria, SUMIF akan mengembalikan lebih dari satu hasil. Dalam contoh yang ditunjukkan, hasil SUMIF adalah larik berikut:
(1,0.5,0,0,0)
Perhatikan bahwa kita benar mendapatkan 1 untuk setiap "F" dan 0,5 untuk setiap "H"., Dan nilai kosong pada minggu itu menghasilkan nol.
Akhirnya, kami menggunakan SUMPRODUCT untuk menjumlahkan nilai dalam larik yang dikembalikan oleh SUMIF. Karena hanya ada satu larik, SUMPRODUCT mengembalikan jumlah semua nilai.
