Formula umum
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Ringkasan
Untuk menjumlahkan n nilai teratas dalam kriteria pencocokan rentang, Anda dapat menggunakan rumus berdasarkan fungsi LARGE, yang dibungkus di dalam fungsi SUMPRODUCT. Dalam bentuk umum rumus (di atas), rentang mewakili rentang sel yang dibandingkan dengan kriteria , nilai mewakili nilai numerik dari mana nilai teratas diambil, dan N mewakili gagasan nilai N.
Dalam contoh, sel aktif berisi rumus ini:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Dimana warna adalah rentang bernama B5: B12 dan nilai adalah rentang bernama C5: C12.
Penjelasan
Dalam bentuknya yang paling sederhana, LARGE mengembalikan nilai "terbesar ke-N" dalam rentang dengan konstruksi ini:
=LARGE (range,N)
Jadi, misalnya:
=LARGE (C5:C12,2)
akan mengembalikan nilai terbesar ke-2 dalam rentang C5: C12, yaitu 12 dalam contoh yang ditampilkan.
Namun, jika Anda memberikan "konstanta array" (misalnya, konstanta dalam bentuk (1,2,3)) ke LARGE sebagai argumen kedua, LARGE akan mengembalikan array hasil, bukan hasil tunggal. Jadi, rumusnya:
=LARGE (C5:C12, (1,2,3))
akan mengembalikan nilai terbesar ke-1, ke-2, dan ke-3 C5: C12 dalam array seperti ini: (12,12,10)
Jadi, triknya di sini adalah memfilter nilai berdasarkan warna sebelum LARGE dijalankan. Kami melakukan ini dengan ekspresi:
(color=E5)
Yang menghasilkan array nilai TRUE / FALSE. Selama operasi perkalian, nilai-nilai ini dipaksakan menjadi satu dan nol:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Jadi hasil akhirnya adalah hanya nilai yang terkait dengan warna "merah" yang bertahan dalam operasi:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
dan nilai lainnya dipaksa menjadi nol.
Catatan: rumus ini tidak akan menangani teks dalam rentang nilai. Lihat di bawah.
Menangani teks dalam nilai
Jika Anda memiliki teks di mana pun dalam rentang nilai, fungsi LARGE akan menampilkan kesalahan #VALUE dan menghentikan rumus agar tidak berfungsi.
Untuk menangani teks dalam rentang nilai, Anda dapat menambahkan fungsi IFERROR seperti ini:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Di sini, kami menjebak kesalahan dari LARGE yang disebabkan oleh nilai teks dan mengganti dengan nol. Menggunakan IF inside LARGE mengharuskan rumus dimasukkan dengan control + shift + enter, jadi kami beralih ke SUM, bukan SUMPRODUCT.
Catatan: Saya menemukan rumus ini yang diposting oleh Barry Houdini yang menakjubkan di stackoverflow.
