Mas @Argha24 ...

Saya coba jelaskan sedikit mengenai masalah formulasi pada file yang mas lampirkan terakhir di atas sebagai berikut:

[A] Formula Pada Sel C5

=SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=$B5)*(INDEX(LOC;0;$C$2)))

— Fungsi MONTH pada dasarnya akan menghasilkan nilai berupa angka (1 sampai dengan 12) sesuai dengan indeks bulan dari tanggal yang menjadi parameternya. Pada formula di atas, notasi MONTH(TGL)=$B5 akan selalu menghasilkan nilai FALSE oleh karena tipe nilai pada sel B5 adalah teks (sel B5 berisi teks JANUARY), bukan angka. Seharusnya, pada sel B5 berisi nilai angka (misalkan 42736 untuk tanggal 01/01/2017). Perlu diperhatikan, meskipun sel B5 sudah berisi nilai angka (tanggal), notasi MONTH(TGL)=$B5 akan tetap menghasilkan nilai FALSE oleh karena seharusnya pada notasi tersebut, nilai yang dibaca dari sel B5 adalah indeks bulannya, bukan tanggal secara keseluruhan. Sehingga, notasi yang tepat seharusnya adalah MONTH(TGL)=MONTH($B5).

— Fungsi INDEX merujuk ke range data dan indeks kolom yang salah. Seharusnya, data yang akan diambil mengacu kepada range B2:R510 pada sheet DATA HUJAN. Sedangkan indeks kolom yang harusnya bertipe angka dirujuk ke sel C2 yang bukan berupa angka, sehingga terjadilah kesalahan #VALUE!. Untuk mendapatkan indeks kolom yang tepat sesuai header kolom pada sel C2, dapat menggunakan fungsi MATCH misalkan MATCH($C$2;LOC;0). Jika terjadi kesalahan dari fungsi MATCH ini (misalkan header tidak ada yang sesuai atau tidak ditemukan), biasanya hasil akhirnya adalah #N/A.

Dengan demikian, pada sel C5, formula yang digunakan seharusnya adalah:

=SUMPRODUCT((YEAR(TGL)=$C$3)*(MONTH(TGL)=MONTH($B5))*INDEX('DATA HUJAN'!$B$2:$R$510;0;MATCH($C$2;LOC;0)))

sedangkan untuk sel B5 (range B5:B10), dapat menggunakan formula yang sudah saya sampaikan sebelumnya:

=DATE($C$3;ROW($A1)+(($E$3=2)*6);1)

[B] Range Data Pada Sheet DATA HUJAN

Perbaikan dari formula pada poin A di atas tidak serta merta membuat hasilnya sesuai. Kesalahan tetap terjadi dikarenakan ada beberapa data pada range B2:R510 pada sheet DATA HUJAN yang berisi tipe nilai bukan angka (Non Numeric Value). Misalkan saja pada sel H105, H106, H116 dan sel lainnya. Solusinya, ubah nilai-nilai Non Numeric tersebut menjadi 0 (nol) atau kosongkan saja.

Selain itu, untuk definisi nama range TGL (range A2:A1048576) sebaiknya dibuat secara dinamis sehingga tidak terlalu banyak baris data kosong yang ikut terkalkulasi dan dapat dibuat menjadi sinkron dengan baris data aktualnya. Pada file yang mas lampirkan terakhir di atas, aktualnya hanya ada 509 baris data saja.

Demikian hal-hal yang dapat saya jelaskan. Terlampir contoh perbaikannya. Silahkan dipelajari dan dimodifikasi kembali. Untuk beberapa sel yang masih ada kesalahan, coba diperbaiki dahulu sendiri ya. Semoga bermanfaat.