Cara Menjumlahkan Data dengan Kriteria Tertentu di Excel

Menjumlahkan data dengan kriteria tertentu di Excel adalah tugas yang sering diperlukan dalam analisis data. Baik itu dalam konteks keuangan, inventaris, atau pelacakan kinerja, kemampuan untuk menyaring dan menjumlahkan data berdasarkan kriteria spesifik sangat penting. Misalnya, Anda mungkin ingin mengetahui total penjualan produk tertentu di wilayah tertentu atau jumlah stok barang dalam kategori tertentu.

Kriteria ini dapat bervariasi dan mencakup kombinasi beberapa faktor seperti tanggal, jenis produk, dan lokasi. Dengan Excel, kita bisa menggunakan berbagai fungsi untuk mencapai tujuan ini, dan salah satu fungsi yang paling efektif adalah SUMPRODUCT.

Ketika kriteria yang digunakan adalah parsial atau tidak lengkap, kita memerlukan pendekatan yang lebih kompleks. Kriteria parsial berarti bahwa kita hanya memiliki sebagian informasi yang diperlukan untuk menyaring data secara tepat. Misalnya, Anda mungkin hanya tahu bagian dari nama produk atau sebagian dari nomor seri barang.

Dalam situasi ini, fungsi SUMPRODUCT menjadi sangat berguna karena kemampuannya untuk bekerja dengan array dan melakukan operasi yang lebih kompleks dibandingkan dengan fungsi penjumlahan sederhana.

Dalam artikel ini, kita akan membahas cara menjumlahkan data dengan kriteria tertentu, misalnya kriteria parsial dengan menggunakan fungsi SUMPRODUCT, sehingga Anda dapat dengan mudah menangani situasi-situasi yang memerlukan analisis data yang lebih mendalam dan akurat.

Memahami Fungsi SUMPRODUCT

Fungsi SUMPRODUCT adalah salah satu fungsi paling kuat di Excel yang memungkinkan Anda melakukan operasi matematika pada array dan menjumlahkan hasilnya. Dengan fungsi ini, Anda dapat mengalikan nilai dalam berbagai range dan kemudian menjumlahkan hasil perkalian tersebut dalam satu langkah yang efisien. Ini sangat berguna ketika Anda bekerja dengan data besar dan kompleks yang membutuhkan analisis mendalam. Misalnya, dalam konteks bisnis, SUMPRODUCT dapat digunakan untuk menjumlahkan data dengan kriteria tertentu atau untuk menganalisis biaya dan pendapatan berdasarkan berbagai faktor.

Fungsi ini sangat berguna ketika Anda perlu menjumlahkan data dengan kriteria tertentu. Dalam dunia nyata, sering kali kita harus melakukan penjumlahan data yang tidak hanya berdasarkan satu kriteria, tetapi beberapa kriteria sekaligus. Misalnya, Anda mungkin ingin menjumlahkan penjualan produk tertentu dalam periode waktu tertentu di lokasi tertentu. Dengan SUMPRODUCT, Anda dapat dengan mudah menerapkan beberapa kriteria ini secara bersamaan. Hal ini menjadikan SUMPRODUCT sebagai alat yang sangat efektif dan fleksibel untuk analisis data yang kompleks, memungkinkan Anda mendapatkan wawasan yang lebih mendalam dari data yang Anda miliki.

Studi Kasus: Menjumlahkan Data dengan Kriteria Parsial

Misalkan Anda memiliki data penjualan celana di lembar kerja Excel yang mencakup berbagai atribut seperti warna, ukuran, dan jumlah penjualan. Tugas Anda adalah menjumlahkan jumlah barang yang memenuhi kriteria parsial tertentu, misalnya, warna “Black” dan ukuran yang dimulai dengan “38”.

Struktur Data

Anggaplah data penjualan celana Anda berada di lembar kerja dengan struktur berikut:

SUMPRODUCT untuk Menjumlahkan Data dengan Kriteria Tertentu

Berikut adalah rumus SUMPRODUCT yang dapat digunakan di sel H3 untuk menjumlahkan data dengan kriteria tertentu yang dalam hal ini “jumlah penjualan” di kolom E berdasarkan kriteria parsial “Black 38” di sel G3:

=SUMPRODUCT(($C$3:$C$20=TRIM(LEFT(SUBSTITUTE(G3;" ";REPT(" ";LEN(G3)));LEN(G3))))*(LEFT($D$3:$D$20;2)=TRIM(RIGHT(SUBSTITUTE(G3;" ";REPT(" ";LEN(G3)));LEN(G3))))*($E$3:$E$20))

Copy dan paste rumus untuk menjumlahkan data dengan kriteria tertentu di atas ke sel yang ada di bawahnya (range H3:H12). Jika rumus yang dimasukkan benar maka hasilnya sebagai berikut:

Penjelasan Struktur Rumus

Rumus Excel berikut adalah cara untuk menjumlahkan data dengan kriteria tertentu di kolom E berdasarkan dua kriteria yang ada di kolom C dan D. Rumus ini menggunakan fungsi SUMPRODUCT untuk melakukan operasi matematika pada array. Mari kita uraikan rumus ini secara detail:

=SUMPRODUCT(($C$3:$C$20=TRIM(LEFT(SUBSTITUTE(G3; " "; REPT(" "; LEN(G3))); LEN(G3)))) * (LEFT($D$3:$D$20; 2)=TRIM(RIGHT(SUBSTITUTE(G3; " "; REPT(" "; LEN(G3))); LEN(G3)))) * ($E$3:$E$20))

Bagian 1

($C$3:$C$20=TRIM(LEFT(SUBSTITUTE(G3; " "; REPT(" "; LEN(G3))); LEN(G3))))

1. SUBSTITUTE(G3, ” “; REPT(” “; LEN(G3))): Mengganti setiap spasi di sel G3 dengan serangkaian spasi panjang yang sama dengan panjang G3. Misalnya, jika G3 berisi “Black 40”, maka setiap spasi akan diganti dengan serangkaian spasi.
2. LEFT(SUBSTITUTE(G3; ” “; REPT(” “; LEN(G3))); LEN(G3)): Mengambil karakter dari sebelah kiri string yang dihasilkan sepanjang panjang G3. Hasilnya adalah kata pertama di G3.
3. TRIM(…): Menghapus spasi tambahan yang ada di sekitar string yang dihasilkan, sehingga kita mendapatkan kata pertama dari G3.
4. ($C$3:$C$20 = …): Membandingkan setiap nilai di kolom C (dari C3 hingga C20) dengan hasil dari langkah sebelumnya. Hasilnya adalah array boolean yang bernilai TRUE jika nilai di kolom C cocok dengan kata pertama dari G3, dan FALSE jika tidak.

Bagian 2

(LEFT($D$3:$D$20; 2)=TRIM(RIGHT(SUBSTITUTE(G3; " "; REPT(" "; LEN(G3))); LEN(G3))))

1. SUBSTITUTE(G3; ” “; REPT(” “; LEN(G3))): Sama seperti sebelumnya, mengganti setiap spasi di G3 dengan serangkaian spasi panjang.
2. RIGHT(SUBSTITUTE(G3; ” “; REPT(” “; LEN(G3))); LEN(G3)): Mengambil karakter dari sebelah kanan string yang dihasilkan sepanjang panjang G3. Hasilnya adalah dua karakter terakhir di G3.
3. TRIM(…): Menghapus spasi tambahan yang ada di sekitar string yang dihasilkan, sehingga kita mendapatkan dua karakter terakhir dari G3.
4. LEFT($D$3:$D$20; 2) = …: Mengambil dua karakter pertama dari setiap nilai di kolom D (dari D3 hingga D20) dan membandingkannya dengan hasil dari langkah sebelumnya. Hasilnya adalah array boolean yang bernilai TRUE jika dua karakter pertama dari nilai di kolom D cocok dengan dua karakter terakhir dari G3, dan FALSE jika tidak.

Bagian 3

($E$3:$E$20)

• Mengacu pada nilai-nilai di kolom E (dari E3 hingga E20) yang akan dijumlahkan jika kedua kriteria sebelumnya (di kolom C dan D) terpenuhi.

Fungsi SUMPRODUCT

• SUMPRODUCT(...):
• Mengalikan array-array yang dihasilkan oleh kedua kriteria boolean dan nilai di kolom E. Array boolean akan memiliki nilai 1 jika kondisinya TRUE dan 0 jika kondisinya FALSE.
• Hasil perkalian ini akan menjumlahkan nilai-nilai di kolom E yang memenuhi kedua kriteria tersebut.

Dengan rumus di atas, jika sel G3 berisi “Black 38”, rumus akan menjumlahkan semua nilai di kolom E yang memiliki “Black” di kolom C dan “38” di kolom D.

Dengan demikian, rumus SUMPRODUCT ini memungkinkan Anda untuk menjumlahkan data dengan kriteria tertentu di Excel berdasarkan beberapa kriteria parsial yang kompleks.

Langkah-langkah Menggunakan Rumus di Excel

1. Siapkan Data: Pastikan data Anda tersusun dengan baik di lembar kerja “Data Mentah” sesuai dengan struktur yang disebutkan.
2. Masukkan Rumus: Tempatkan rumus di sel di mana Anda ingin hasil penjumlahan ditampilkan. Misalkan di sel B8, masukkan rumus di atas.
3. Uji Rumus: Cobalah dengan beberapa contoh data untuk memastikan rumus bekerja dengan benar. Misalkan, jika A8 berisi “Black 40”, rumus akan menjumlahkan semua baris yang memenuhi kriteria tersebut.

Kesimpulan

Menggunakan SUMPRODUCT dengan kriteria parsial di Excel memungkinkan Anda melakukan analisis data yang kompleks dengan cara yang efisien. Dengan memahami cara kerja rumus untuk menjumlahkan data dengan kriteria tertentu ini, Anda dapat mengaplikasikannya untuk berbagai skenario dan kebutuhan data Anda. Pastikan untuk menyesuaikan rentang data dan kriteria sesuai dengan kebutuhan spesifik Anda.

Dengan teknik ini, Anda dapat menjumlahkan data dengan kriteria tertentu, bahkan multi-kriteria di Excel dengan lebih mudah dan akurat, bahkan ketika kriteria tersebut hanya parsial.

Pranala Luar

File Excel Cara menjumlahkan data dengan kriteria tertentu di Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul video: Cara Menjumlah Data dengan Kriteria Parsial di Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!