Ringkasan
Untuk menjumlahkan menurut bulan dalam kolom, Anda dapat menggunakan fungsi SUMIFS bersama dengan fungsi EOMONTH. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Rumus ini menggunakan tiga rentang bernama: "jumlah" (D5: D15), "klien" (B5: B15), dan "tanggal" (C5: C15).
Penjelasan
Fungsi SUMIFS dirancang untuk menjumlahkan nilai dalam rentang berdasarkan satu atau beberapa kriteria. Dalam hal ini, kami membutuhkan tiga kriteria:
- Nama klien = klien di kolom F
- Tanggal> = bulan pertama (dari tanggal di baris 4)
- Tanggal <= akhir bulan (dari tanggal di baris 4)
Poin utama: nama bulan di G4: I4 sebenarnya adalah tanggal yang valid, diformat dengan format angka khusus "mmm". Ini memungkinkan kita untuk menggunakan nilai tanggal di G4: I4 secara langsung untuk kriteria # 2 dan # 3 di atas.
Kembali ke SUMIFS, pasangan rentang / kriteria pertama digunakan untuk memeriksa nama klien:
client,$F5
Perhatikan F5 adalah referensi campuran, dengan kolom terkunci. Hal ini memungkinkan baris berubah saat rumus disalin melalui tabel, tetapi nama klien selalu berasal dari kolom F.
Pasangan rentang / kriteria kedua digunakan untuk memeriksa tanggal terhadap tanggal pertama setiap bulan:
date,">="&G$4
Seperti disebutkan di atas, nilai dalam G4: I4 sebenarnya adalah tanggal: 1 Jan 2019, 1 Feb 2019, dan 1 Mar 2019. Jadi, kriteria ini hanya memeriksa tanggal yang lebih besar dari tanggal pertama bulan di baris 4. Perhatikan bahwa ini adalah referensi campuran lainnya, kali ini dengan baris terkunci. Ini memungkinkan kolom untuk berubah saat rumus disalin ke seluruh tabel, tetapi tetap mempertahankan nomor baris. Penggabungan dengan ampersand (&) diperlukan saat membangun kriteria yang menggunakan operator logika dan nilai dari sel lain.
Pasangan rentang / kriteria ketiga digunakan untuk memeriksa tanggal terhadap hari terakhir bulan:
date,"<="&EOMONTH(G$4,0)
Untuk mendapatkan hari terakhir setiap bulan, kami menggunakan fungsi EOMONTH pada tanggal dari tajuk kolom di baris 4. Dengan nol disediakan untuk argumen bulan, EOMONTH mengembalikan hari terakhir di bulan yang sama. Sekali lagi, referensi ke G4 dicampur agar baris tidak berubah.
Solusi Tabel Pivot
Tabel pivot akan menjadi solusi yang sangat baik untuk masalah ini, karena dapat dikelompokkan secara otomatis berdasarkan bulan tanpa rumus sama sekali. Untuk perbandingan berdampingan dari rumus vs. tabel pivot, lihat video ini: Mengapa tabel pivot.
