3 Cara Menghitung Data Rentang Usia di Excel Lengkap dengan Contohnya

3 Cara Menghitung Data Rentang Usia di Excel – Pernahkah Anda mengalami situasi di mana Anda perlu menghitung jumlah data yang memenuhi dua kriteria sekaligus: berada dalam rentang tanggal tertentu dan juga termasuk dalam rentang usia tertentu? Misalnya, menghitung jumlah subscriber atau follower yang bergabung selama tahun 2024 dan berusia antara 21 hingga 30 tahun. Untuk hal ini, Excel memiliki berbagai solusi yang bisa Anda gunakan. Artikel ini akan membahas 3 cara menghitung data rentang usia di Excel dengan bantuan fungsi-fungsi andalan seperti COUNTIFS, SUMPRODUCT, dan TEXTBEFORE/TEXTAFTER.

Kenapa Perlu Menggunakan Rentang Tanggal dan Rentang Usia?

Menggabungkan dua kriteria ini sangat penting, khususnya di dunia kerja, riset pemasaran, atau pengolahan data kependudukan. Namun sayangnya, banyak orang belum tahu cara menghitung data rentang usia di Excel secara efisien. Salah satu tantangan terbesar adalah karena rentang usia sering ditulis dalam bentuk teks, seperti “0-20”, “21-30”, dsb. Excel tidak bisa langsung memahami ini sebagai angka, sehingga perlu penguraian terlebih dahulu.

Contoh Studi Kasus

Bayangkan Anda memiliki data subscriber dengan dua kolom penting:

• Kolom Tanggal Subscribe/Follow
• Kolom Usia Subscriber

Tujuan Anda adalah mengetahui berapa subscriber yang bergabung antara 1 Januari 2024 sampai 31 Desember 2024, dan termasuk dalam rentang usia tertentu (seperti 0-20 tahun, 21–30 tahun dan 31-40 tahun). Berikut contoh tabelnya:

Solusi 1: Menggunakan Fungsi COUNTIFS

Fungsi COUNTIFS sangat ideal untuk menghitung data berdasarkan banyak kriteria.

Langkah 1: Hitung Berdasarkan Rentang Tanggal

=COUNTIFS($D$3:$D$12;">="&$H$2;$D$3:$D$12;"<="&$H$3)

Formula ini akan menghitung jumlah baris di mana tanggal bergabung berada antara tanggal start dan end.

Langkah 2: Tambahkan Kriteria Rentang Usia

Misalnya, rentang usia ditulis di sel G8 sebagai “21-30”. Maka formula menjadi:

=COUNTIFS($D$3:$D$12;">="&$H$2;$D$3:$D$12;"<="&$H$3;$E$3:$E$12;">="&LEFT(G6;FIND("-";G6)-1);$E$3:$E$12;"<="&RIGHT(G6;LEN(G6)-FIND("-";G6)))

Jika benar, maka hasilnya adalah sebagai berikut:

Penjelasan:

• LEFT(G6;FIND("-";G6)-1) mengekstrak angka batas bawah (misal: 21).
• RIGHT(G6;LEN(G6)-FIND("-";G6)) mengekstrak angka batas atas (misal: 30).
• Keduanya digunakan sebagai kriteria untuk kolom usia.

Ini adalah cara menghitung data rentang usia di Excel yang paling umum dan mudah dipahami, terutama jika Anda ingin hasil langsung berdasarkan dua kriteria.

Solusi 2: Menggunakan Fungsi SUMPRODUCT

Fungsi SUMPRODUCT sangat fleksibel dan cocok untuk perhitungan dengan array.

Tanpa Rentang Usia

=SUMPRODUCT(($D$3:$D$12>=$H$2)*($D$3:$D$12<=$H$3))

Dengan Rentang Usia

=SUMPRODUCT(($D$3:$D$12>=$H$2)*($D$3:$D$12<=$H$3)*($E$3:$E$12>=LEFT(G6;FIND("-";G6)-1)+0)*($E$3:$E$12<=RIGHT(G6;LEN(G6)-FIND("-";G6))+0))

Perhatikan bahwa kita menambahkan +0 di akhir LEFT dan RIGHT. Ini penting karena fungsi tersebut mengembalikan teks, sedangkan SUMPRODUCT memerlukan angka. Menambahkan +0 mengonversi teks menjadi angka.

Metode ini juga merupakan salah satu cara menghitung data rentang usia di Excel yang akurat dan sangat fleksibel untuk digunakan dalam tabel dinamis. Berikut hasil akhirmya:

Solusi 3: Memanfaatkan TEXTBEFORE dan TEXTAFTER (Excel 365)

Jika Anda pengguna Excel 365 atau Excel 2024, Anda bisa menyederhanakan formula dengan fungsi TEXTBEFORE dan TEXTAFTER.

COUNTIFS Versi Ringkas:

=COUNTIFS($D$3:$D$12;">="&$H$2;$D$3:$D$12;"<="&$H$3;$E$3:$E$12;">="&TEXTBEFORE(G6;"-");$E$3:$E$12;"<="&TEXTAFTER(G6;"-"))

SUMPRODUCT Versi Ringkas:

=SUMPRODUCT(($D$3:$D$12>=$H$2)*($D$3:$D$12<=$H$3)*($E$3:$E$12>=TEXTBEFORE(G6;"-")+0)*($E$3:$E$12<=TEXTAFTER(G6;"-")+0))

Fungsi TEXTBEFORE(G6;"-") akan mengembalikan teks sebelum tanda “-“, misalnya “21”, dan TEXTAFTER(G6;"-") akan mengembalikan “30”. Ini sangat memudahkan karena Anda tidak perlu lagi menggunakan LEFT, RIGHT, dan FIND.

Solusi ini sangat cocok untuk Anda yang mencari cara tercepat dan paling modern dalam cara menghitung data rentang usia di Excel. Berikut hasilnya:

Tips Tambahan

• Pastikan data usia dalam bentuk angka, bukan teks. Jika berupa teks, Anda bisa gunakan --age atau VALUE(age) dalam formula.
• Hindari spasi berlebih di sel rentang usia seperti “21 – 30”, karena akan menyebabkan error pada FIND, TEXTBEFORE, atau TEXTAFTER.
• Gunakan named ranges (joined, age, start, end) untuk membuat formula lebih rapi dan mudah dibaca.

Kesimpulan

Kini Anda memiliki 3 cara menghitung data rentang usia di Excel yang bisa Anda sesuaikan dengan kebutuhan dan versi Excel Anda:

1. COUNTIFS: Solusi klasik dan langsung, sangat cocok untuk laporan cepat.
2. SUMPRODUCT: Lebih fleksibel dan bisa menangani skenario kompleks.
3. TEXTBEFORE & TEXTAFTER: Solusi modern bagi pengguna Excel 365 dan Excel 2024.

Apapun metode yang Anda pilih, prinsip dasarnya sama: pecahkan kriteria rentang usia menjadi dua angka, lalu gabungkan dengan rentang tanggal untuk mendapatkan hasil yang tepat.

Dengan menguasai teknik ini, Anda akan lebih percaya diri dalam membuat laporan dinamis berbasis waktu dan usia. Jadi, jangan ragu untuk mempraktikkannya sekarang juga!

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempraktikkan Cara Menghitung Data Rentang Usia di Excel ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!