Cara Menjumlah Angka di Excel Berdasarkan Kriteria Teks Tertentu – Microsoft Excel adalah salah satu alat yang sangat kuat untuk mengelola dan menganalisis data. Dengan berbagai fitur canggih yang ditawarkannya, Excel telah menjadi alat yang tak tergantikan bagi banyak profesional, pelajar, dan bahkan pengguna rumahan. Kemampuan untuk memproses data dalam jumlah besar dengan cepat dan akurat menjadikan Excel pilihan utama untuk berbagai kebutuhan, mulai dari pelaporan hingga analisis keuangan.
Salah satu fitur penting dalam Excel adalah kemampuan untuk menjumlahkan angka berdasarkan kriteria tertentu. Fungsi ini sangat berguna dalam situasi di mana data yang dikelola memiliki pola atau kategori tertentu yang perlu dianalisis lebih lanjut. Misalnya, Anda mungkin ingin mengetahui total penjualan produk yang mengandung kata kunci tertentu atau menghitung jumlah transaksi yang sesuai dengan kategori tertentu dalam dataset Anda, maka Cara Menjumlah Angka di Excel ini adalah solusi yang tepat.
Dalam artikel ini, kita akan membahas cara menjumlah angka di Excel dengan kriteria yang mengandung kata tertentu. Untuk mencapai tujuan ini, kita akan memanfaatkan kombinasi fungsi SUMPRODUCT, ISNUMBER, dan SEARCH. Pendekatan ini menawarkan fleksibilitas yang tidak dimiliki oleh fungsi SUMIFS biasa, terutama ketika kriteria yang digunakan melibatkan pencarian substring dalam teks. Dengan pemahaman yang tepat tentang teknik ini, Anda dapat meningkatkan efisiensi dan akurasi analisis data Anda di Excel.
Contoh Kasus Cara Menjumlah Angka di Excel
Misalkan Anda memiliki daftar produk di kolom C dan penjualannya di kolom D. Tujuannya adalah menjumlahkan penjualan produk yang mengandung kata “Goreng” atau “ayam” di nama produknya. Contoh data:
Dengan menggunakan rumus tertentu, kita akan menjumlahkan semua penjualan produk yang mengandung “Goreng” atau “Ayam”.
Rumus yang Digunakan
Rumus yang digunakan untuk kasus ini adalah:
=SUMPRODUCT(--((ISNUMBER(SEARCH("Goreng";C3:C15)) + ISNUMBER(SEARCH("Ayam";C3:C15)))>0);D3:D15)
Dan jika benar, maka hasilnya akan seperti berikut:
Penjelasan Rumus
Mari kita uraikan bagaimana rumus Cara Menjumlah Angka di Excel ini bekerja:
1. SEARCH dan ISNUMBER:
- Fungsi
SEARCH
digunakan untuk mencari substring dalam teks. Jika substring ditemukan, ia mengembalikan posisi sebagai angka. Jika tidak ditemukan, ia mengembalikan kesalahan (#VALUE!). - Fungsi
ISNUMBER
kemudian digunakan untuk mengubah hasilSEARCH
menjadi nilai logika. Jika substring ditemukan,ISNUMBER
mengembalikanTRUE
; jika tidak,FALSE
. - Contoh:
- =
ISNUMBER(SEARCH("Goreng";C3
)) untuk sel dengan teks “Indomie Goreng Rasa Ayam Panggang” akan mengembalikanTRUE
. =ISNUMBER(SEARCH("Ayam";C3))
untuk sel yang sama akan mengembalikanFALSE
.
- =
2. Menggabungkan Kriteria:
- Kita menjumlahkan hasil
ISNUMBER(SEARCH("Goreng",...))
danISNUMBER(SEARCH("Ayam",...))
. Karena nilai logikaTRUE
danFALSE
diperlakukan sebagai 1 dan 0 dalam operasi matematika, penjumlahan ini menghasilkan 1 jika salah satu kriteria terpenuhi atau 2 jika keduanya terpenuhi. - Contoh:
- Jika sel mengandung “Goreng” saja, hasilnya adalah 1.
- Jika sel mengandung “Ayam” saja, hasilnya adalah 1.
- Jika sel mengandung keduanya, seperti teks “Indomie Goreng Rasa Ayam Geprek” hasilnya adalah 2.
3. Menghindari Penghitungan Ganda:
- Untuk memastikan setiap sel hanya dihitung sekali meskipun mengandung kedua substring, kita membandingkan hasil penjumlahan dengan nol (
>0
). Ini mengubah semua nilai non-nol menjadiTRUE
, danFALSE
untuk nilai nol.
4. SUMPRODUCT:
- Fungsi
SUMPRODUCT
mengalikan array hasil logika dengan array angka di kolom penjualan (D3:D15) dan menjumlahkan hasilnya. - Contoh:
- Array logika:
{1;0;1;0;0;1;1;0;1;0;1;1}
- Array penjualan:
{2;5;3;3;4;5;1;4;2;5;1;5}
- Hasil perkalian:
{2;0;3;0;0;5;1;0;2;0;1;5}
- Total:
2 + 3 + 5 + 1 + 2 + 1 + 5 = 23
- Array logika:
Alternatif Sensitif Huruf
Secara default, fungsi SEARCH
tidak sensitif terhadap huruf besar/kecil. Jika Anda membutuhkan pencarian yang sensitif, gunakan fungsi FIND
sebagai pengganti SEARCH
.
Contoh rumus:
=SUMPRODUCT(--((ISNUMBER(FIND("Goreng";C3:C15)) + ISNUMBER(FIND("Ayam";C3:C15)))>0);D3:D15)
Dan berikut adalah hasilnya:
Kesimpulan
Dengan menggunakan kombinasi fungsi SUMPRODUCT
, ISNUMBER
, dan SEARCH
, Anda dapat menjumlahkan angka berdasarkan kriteria yang mengandung kata tertentu di Excel. Pendekatan ini sangat fleksibel dan memungkinkan Anda mengatasi keterbatasan fungsi SUMIFS
. Anda dapat mengembangkan metode ini untuk menangani lebih banyak kriteria atau kasus lain yang memerlukan logika OR.
Cobalah praktikkan rumus Cara Menjumlah Angka di Excel ini pada data Anda untuk melihat bagaimana ia bekerja. Dengan pemahaman yang baik, Anda dapat menggunakan teknik ini untuk menganalisis data dengan lebih efisien di Excel.
Pranala Luar
Dan terakhir, untuk yang membutuhkan file contoh dalam artikel Cara Menjumlah Angka di Excel Berdasarkan Kriteria Teks Tertentu 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!
0 Komentar