Jumlahkan nilai berdasarkan tahun di Excel
Misalkan, Anda memiliki berbagai data yang berisi daftar tanggal dan jumlah penjualan, sekarang Anda ingin mendapatkan jumlah total berdasarkan tahun tertentu seperti gambar di bawah ini. Di Excel, fungsi SUMIFS dan SUMPRODUCT normal dapat membantu menyelesaikan tugas ini dengan cepat dan mudah. Tutorial ini akan mendemonstrasikan cara menggunakan kedua fungsi ini secara detail.
- Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMIFS dan DATE
- Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMPRODUCT
Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMIFS dan DATE
Untuk menjumlahkan nilai total berdasarkan tahun tertentu, Anda harus menentukan tanggal pertama dan terakhir tahun tersebut dengan menggunakan fungsi DATE, dan kemudian menggunakan fungsi SUMIFS untuk menjumlahkan semua nilai dalam tanggal yang ditentukan, sintaks generiknya adalah:
- sum_range: Rentang nilai yang akan dijumlahkan;
- date_range: Rentang tanggal yang akan diuji untuk tahun tertentu;
- year: Tahun tertentu Anda ingin menjumlahkan nilai berdasarkan. (tahun, 1,1 menunjukkan hari pertama tahun itu, dan tahun,12,31 menunjukkan hari terakhir tahun ini.)
Untuk menyelesaikan tugas ini di Excel, silakan salin atau masukkan rumus di bawah ini ke dalam sel kosong, lalu seret rumus ke sel lain untuk mendapatkan hasilnya:
Penjelasan dari rumus ini:
=SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31))
- $ C $ 2: $ C $ 12: adalah “sum_range” yang berisi nilai yang ingin kita jumlahkan.
- $B$2:$B$12,">="&DATE(E2,1,1): memeriksa tanggal dalam rentang B2:B12 yang lebih besar dari atau sama dengan 01/01/2020 – hari pertama tahun ini di sel E2.
- $B$2:$B$12,"<="&DATE(E2,12,31): memeriksa tanggal dalam rentang B2:B12 yang kurang dari atau sama dengan 12/31/2020 – hari terakhir dalam setahun di sel E2.
- SUMIFS($C$2:$C$12,$B$2:$B$12,">="&DATE(E2,1,1),$B$2:$B$12,"<="&DATE(E2,12,31)):Setelah memenuhi kriteria, nilai dalam rentang C2:C12 akan ditemukan dan dijumlahkan.
Jumlahkan nilai berdasarkan tahun di Excel dengan fungsi SUMPRODUCT
Di sini, rumus sederhana lain yang dibuat oleh fungsi SUMPRODUCT juga dapat membantu Anda, sintaks generiknya adalah:
- date_range: Rentang tanggal yang akan diuji untuk tahun tertentu;
- year: Tahun tertentu Anda ingin menjumlahkan nilai berdasarkan;
- sum_range: Rentang nilai yang akan dijumlahkan.
Silakan salin atau masukkan rumus berikut ke dalam sel kosong, lalu seret ke bawah rumus ke sel lain untuk mendapatkan hasilnya:
Penjelasan dari rumus ini:
=SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)
- TAHUN($B$2:$B$12)=E2:Fungsi YEAR mengembalikan nomor tahun dari setiap sel tanggal dalam rentang B2:B12. Ekspresi ini memeriksa apakah tahun di E2 sama dengan tahun dalam rentang B2:B12. Dan Anda akan mendapatkan array seperti ini: {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}.
- (YEAR($B$2:$B$12)=E2)*$C$2:$C$12={TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE}*$C$2:$C$12: Dalam ekspresi ini, TRUEs dan FALSEs akan dipaksa menjadi 1s dan 0, dan array ini dikalikan dengan setiap jumlah dalam rentang C2:C12, dan Anda akan mendapatkan array baru seperti ini: {347;0;398;430; 0;379;412;0;0;420;0} .
- SUMPRODUCT((YEAR($B$2:$B$12)=E2)*$C$2:$C$12)= SUMPRODUCT({347;0;398;430;0;379;412;0;0;420;0}): Fungsi SUMPRODUCT akhirnya menjumlahkan semua angka dalam larik ini dan mengembalikan hasilnya sebagai 2386.
Fungsi relatif yang digunakan:
- SUMIFS:
- Fungsi SUMIFS di Excel dapat membantu menjumlahkan nilai sel berdasarkan beberapa kriteria.
- SUMPRODUK:
- Fungsi SUMPRODUCT bisa digunakan untuk mengalikan dua atau lebih kolom atau larik bersama-sama, lalu mendapatkan jumlah produk.
- TANGGAL:
- Fungsi DATE dapat membantu kita untuk menggabungkan nomor tahun, bulan dan hari dari sel yang terpisah ke tanggal yang valid.
- TAHUN:
- Fungsi YEAR mengembalikan tahun berdasarkan tanggal yang ditentukan dalam format nomor seri 4 digit.
Artikel lainnya:
- Jumlahkan Nilai Berdasarkan Bulan (Dengan Atau Tanpa Tahun)
- Tutorial ini mendemonstrasikan dua rumus secara rinci untuk membantu menjumlahkan nilai berdasarkan bulan dan tahun tertentu atau menjumlahkan nilai berdasarkan bulan tertentu mengabaikan tahun di Excel.
- Jumlahkan Nilai Berdasarkan Minggu Dari Tanggal yang Diberikan
- Untuk menjumlahkan nilai menurut minggu di mana tanggal tertentu jatuh, Anda bisa menerapkan rumus berdasarkan fungsi SUMIFS.
- Jumlahkan Nilai Berdasarkan Weeknum Atau Weekday
- Tutorial ini menunjukkan rumus langkah demi langkah untuk membantu Anda menjumlahkan nilai dalam rentang tertentu berdasarkan nomor minggu atau hari kerja yang ditentukan di Excel.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
