Formula umum
=SUMPRODUCT(--(MONTH(birthday)=number))
Ringkasan
Untuk menghitung jumlah ulang tahun dalam daftar, Anda bisa menggunakan rumus berdasarkan fungsi SUMPRODUCT dan MONTH. Dalam contoh yang ditunjukkan, E5 berisi rumus ini:
=SUMPRODUCT(--(MONTH(birthday)=D5))
Rumus ini menghitung ulang tahun di bulan Januari (karena D5 berisi 1) dalam rentang bernama "ulang tahun" (B5: B104).
Penjelasan
Anda akan berpikir Anda bisa menggunakan fungsi COUNTIF untuk menghitung ulang tahun, tetapi masalahnya adalah COUNTIF hanya bekerja dengan rentang, dan tidak akan membiarkan Anda menggunakan sesuatu seperti MONTH untuk mengekstrak hanya nomor bulan dari tanggal. Jadi, kami menggunakan SUMPRODUCT sebagai gantinya.
Di dalam SUMPRODUCT, kami memiliki ekspresi ini:
MONTH(birthday)=D5)
Fungsi MONTH mengekstrak bulan untuk setiap tanggal dalam rentang bernama "ulang tahun", dan ini dibandingkan dengan nilai di D5, yaitu 1. Hasilnya adalah larik nilai TRUE / FALSE di mana setiap TRUE mewakili tanggal di mana bulan = 1.
Nilai TRUE FALSE kemudian dikonversi menjadi satu dan nol dengan negatif ganda (-). SUMPRODUCT lalu menjumlahkan angka-angka ini dan mengembalikan hasil akhir.
Berurusan dengan sel kosong
Jika Anda memiliki sel kosong dalam daftar ulang tahun, Anda akan mendapatkan hasil yang salah, karena MONTH (0) mengembalikan 1. Untuk menangani sel kosong, Anda dapat menyesuaikan rumus sebagai berikut:
=SUMPRODUCT((MONTH(birthdays)=D5)*(birthdays""))
Mengalikan dengan ekspresi (ulang tahun "") secara efektif membatalkan nilai bulan untuk sel kosong. Lihat halaman SUMPRODUCT untuk informasi selengkapnya tentang cara kerja ekspresi logis di dalam SUMPRODUCT.
Solusi tabel pivot
Tabel pivot juga merupakan solusi terbaik untuk masalah ini.
