Formula umum
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Ringkasan
Untuk menjumlahkan data menurut bulan, mengabaikan tahun, Anda bisa menggunakan rumus berdasarkan fungsi SUMPRODUCT dan MONTH. Pada contoh yang ditampilkan, rumus di H6 adalah:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Hasilnya adalah total semua penjualan di bulan Maret, tahun mengabaikan.
Penjelasan
Kumpulan data ini berisi lebih dari 2900 rekaman, dan rumus di atas menggunakan dua rentang bernama:
dates = B5:B2932 amounts = E5:E2932
Di dalam fungsi SUMPRODUCT, fungsi MONTH digunakan untuk mengekstrak nomor bulan untuk setiap tanggal dalam kumpulan data, dan membandingkannya dengan angka 3:
(MONTH(dates)=3)
Jika kita mengasumsikan kumpulan data kecil yang mencantumkan 3 tanggal masing-masing di bulan Januari, Februari, dan Maret (dalam urutan itu), hasilnya adalah larik yang berisi sembilan angka seperti ini:
(1;1;1;2;2;2;3;3;3)
dimana setiap angka adalah "angka bulan" untuk sebuah tanggal. Jika nilainya dibandingkan dengan 3, hasilnya adalah array seperti ini:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Larik ini kemudian dikalikan dengan nilai jumlah yang terkait dengan setiap tanggal Maret. Jika kita mengasumsikan kesembilan jumlah sama dengan 100, operasi akan terlihat seperti ini:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Perhatikan operasi matematika mengubah nilai TRUE FALSE menjadi satu dan nol. Setelah perkalian, kami memiliki satu larik di SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Perhatikan bahwa jumlah yang bertahan hanya dikaitkan dengan Maret, sisanya nol.
Terakhir, SUMPRODUCT mengembalikan jumlah semua item - 300 dalam contoh singkat di atas, dan 25.521 pada tangkapan layar dengan data aktual.
Hitung per bulan mengabaikan tahun
Untuk menghitung per bulan mengabaikan tahun, Anda dapat menggunakan SUMPRODUCT seperti ini:
=SUMPRODUCT(--(MONTH(dates)=3))
Rata-rata per bulan mengabaikan tahun
Untuk menghitung dan rata-rata per bulan mengabaikan tahun, Anda menggabungkan dua rumus SUMPRODUCT di atas seperti ini:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
