Formula umum
=SUMPRODUCT(data*(headers=A1))
Ringkasan
Untuk menjumlahkan nilai dalam kolom dengan mencocokkan tajuk kolom, Anda dapat menggunakan rumus yang didasarkan pada fungsi SUMPRODUCT. Pada contoh yang ditampilkan, rumus di J5 adalah:
=SUMPRODUCT(data*(LEFT(headers)=J4))
di mana "data" adalah rentang bernama B5: G14, dan "header" adalah rentang bernama B4: G4.
Rumus menjumlahkan kolom di mana header dimulai dengan "a" dan mengembalikan 201.
Penjelasan
Pada intinya, rumus ini bergantung pada fungsi SUMPRODUCT untuk menjumlahkan nilai di kolom yang cocok dalam rentang bernama "data" C5: G14. Jika semua data diberikan ke SUMPRODUCT dalam satu rentang, hasilnya adalah jumlah semua nilai dalam rentang tersebut:
=SUMPRODUCT(data) // all data, returns 387
Untuk menerapkan filter dengan mencocokkan tajuk kolom - kolom dengan tajuk yang dimulai dengan "A" - kami menggunakan fungsi LEFT seperti ini:
LEFT(headers)=J4) // must begin with "a"
Ekspresi ini mengembalikan TRUE jika header kolom dimulai dengan "a", dan FALSE jika tidak. Hasilnya adalah sebuah array:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Anda dapat melihat bahwa nilai 1,2, dan 5 sesuai dengan kolom yang diawali dengan "a".
Di dalam SUMPRODUCT, larik ini dikalikan dengan "data". Karena penyiaran, hasilnya adalah larik dua dimensi seperti ini:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Jika kita memvisualisasikan larik ini dalam tabel, mudah untuk melihat bahwa hanya nilai dalam kolom yang dimulai dengan "a" yang bertahan dari operasi, semua kolom lainnya adalah nol. Dengan kata lain, filter mempertahankan nilai yang diminati dan "membatalkan" sisanya:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Dengan hanya satu larik untuk diproses, SUMPRODUCT mengembalikan jumlah semua nilai, 201.
Jumlahkan dengan pencocokan persis
Contoh di atas menunjukkan cara menjumlahkan kolom yang dimulai dengan satu atau beberapa karakter tertentu. Untuk menjumlahkan kolom berdasarkan pencocokan tepat, Anda dapat menggunakan rumus yang lebih sederhana seperti ini:
=SUMPRODUCT(data*(headers=J4))
