Formula umum
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Ringkasan
Untuk menjumlahkan nilai n bawah dalam kriteria pencocokan rentang, Anda bisa menggunakan rumus array berdasarkan fungsi SMALL, yang dibungkus di dalam fungsi SUM. Dalam bentuk umum rumus (di atas), range1 mewakili rentang sel dibandingkan dengan kriteria , range2 berisi nilai numerik dari mana nilai terbawah diambil, dan N mewakili "n".
Dalam contoh, sel aktif berisi rumus ini:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Di mana warna adalah rentang bernama B5: B12 dan nilainya adalah rentang bernama C5: C12.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Dalam bentuknya yang paling sederhana, SMALL mengembalikan nilai "terkecil ke-N" dalam rentang dengan konstruksi ini:
=SMALL (range,N)
Jadi, misalnya:
=SMALL (C5:C12,2)
akan mengembalikan nilai terkecil ke-2 dalam rentang C5: C12, yaitu 5 dalam contoh yang ditampilkan.
Namun, jika Anda memberikan "konstanta array" (misalnya, konstanta dalam bentuk (1,2,3)) ke SMALL sebagai argumen kedua, SMALL akan mengembalikan array hasil, bukan hasil tunggal. Jadi, rumusnya:
=SMALL (C5:C12, (1,2,3))
akan mengembalikan nilai terkecil ke-1, ke-2, dan ke-3 C5: C12 dalam larik seperti ini: (4,5,7).
Jadi, triknya di sini adalah memfilter nilai berdasarkan warna sebelum SMALL dijalankan. Kami melakukan ini dengan ekspresi berdasarkan fungsi IF:
IF(color=E5,value)
Ini membangun larik nilai yang dimasukkan ke dalam SMALL. Pada dasarnya, hanya nilai yang terkait dengan warna merah yang dimasukkan ke dalam array. Jika warna sama dengan "merah", array berisi angka, dan jika warnanya bukan merah, array tersebut berisi FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Fungsi SMALL mengabaikan nilai FALSE dan mengembalikan 3 nilai terkecil dalam array: (4,8,10). Fungsi SUM mengembalikan hasil akhir, 22.
