Formula generik
=SUMPRODUCT(SUMIF(codes,lookups,values))
Ringkasan
Untuk menjumlahkan nilai yang diambil oleh operasi carian, anda boleh menggunakan SUMPRODUCT dengan fungsi SUMIF.
Dalam contoh yang ditunjukkan, formula dalam H5 adalah:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Di mana kod adalah julat bernama J4: J5 dan nilai adalah julat bernama K4: K5.
Konteks
Kadang-kadang anda mungkin ingin menjumlahkan beberapa nilai yang diperoleh dengan operasi mencari. Dalam contoh ini, kami ingin menjumlahkan waktu percutian yang diambil setiap minggu berdasarkan sistem kod, di mana F = sehari penuh, dan H = setengah hari. Sekiranya hari kosong, tidak ada masa yang diambil.
Cabarannya adalah mencari formula yang sama-sama mencari dan menjumlahkan nilai yang berkaitan dengan F dan H.
Penjelasan
Inti formula 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 berfungsi dengan baik ketika nilainya berangka, dan tidak ada pendua dalam "jadual carian ".
Caranya dalam hal ini ialah kriteria untuk SUMIF bukan satu nilai, melainkan susunan nilai dalam julat C5: G5:
=SUMPRODUCT(SUMIF(codes,C5:G5,values))
Kerana kami memberikan SUMIF lebih dari satu kriteria, SUMIF akan mengembalikan lebih dari satu hasil. Dalam contoh yang ditunjukkan, hasil SUMIF adalah susunan berikut:
(1,0.5,0,0,0)
Perhatikan bahawa kita mendapat 1 dengan betul untuk setiap "F" dan 0,5 untuk setiap "H"., Dan nilai kosong dalam minggu menghasilkan sifar.
Akhirnya, kami menggunakan SUMPRODUCT untuk menambahkan nilai dalam array yang dikembalikan oleh SUMIF. Kerana hanya ada satu array, SUMPRODUCT hanya mengembalikan jumlah semua nilai.
