Cara Menjumlah Data dengan Kriteria Pengecualian di Microsoft Excel

Cara Menjumlah Data dengan Kriteria Pengecualian – Dalam pengolahan data menggunakan Microsoft Excel, kita sering kali dihadapkan pada kebutuhan untuk menjumlahkan data dengan kriteria tertentu. Misalnya, menjumlahkan total penjualan berdasarkan produk, wilayah, atau periode tertentu. Namun, bagaimana jika kita ingin menjumlah data dengan kriteria pengecualian? Artinya, kita ingin menghitung total dari semua data, kecuali beberapa item yang sengaja dikecualikan.

Pada artikel ini, kita akan membahas secara lengkap cara menjumlah data dengan kriteria pengecualian di Excel, menggunakan kombinasi rumus SUM, SUMIF, dan SUMPRODUCT. Contoh yang digunakan berdasarkan tabel penjualan yang terlampir.

Contoh Kasus: Laporan Penjualan dengan Pengecualian Produk

Misalkan kita memiliki tabel penjualan dengan sisi kanan tabel terdapat daftar produk yang tidak ingin kita sertakan dalam perhitungan seperti berikut:

Hasil akhir menunjukkan total penjualan selain produk yang ada dalam daftar pengecualian.

Rumus yang Digunakan

Untuk menghitung total dengan kriteria pengecualian, kita menggunakan rumus berikut:

=SUM(F3:F19)-SUMPRODUCT(SUMIF(C3:C19;H3:H5;F3:F19))

Rumus ini adalah kunci utama dari cara menjumlah data dengan kriteria pengecualian di Excel. Mari kita bahas bagian per bagian agar lebih jelas.

Penjelasan Struktur Rumus

1. SUM(F3:F19)

• Fungsi SUM menjumlahkan semua nilai di kolom F (Jumlah).
• Artinya: menghitung total keseluruhan penjualan dari semua produk.

2. SUMIF(C3:C19;H3:H5;F3:F19)

• Fungsi SUMIF digunakan untuk menjumlahkan data berdasarkan kriteria.
• Bagian pertama C3:C19 adalah range kriteria (Produk Terjual).
• Bagian kedua H3:H5 adalah daftar kriteria (produk yang ingin dikecualikan).
• Bagian ketiga F3:F19 adalah range yang dijumlahkan (Jumlah penjualan).
• Karena kriterianya berupa array (H3:H5), hasil dari SUMIF juga berupa array (beberapa nilai). Misalnya:
• Daihatsu Xenia 2020 → Rp 2.870.000.000
• Honda Jazz 2018 → Rp 840.000.000
• Suzuki Ertiga 2019 → Rp 2.625.000.000

3. SUMPRODUCT(SUMIF(...))

• SUMPRODUCT berfungsi menjumlahkan array hasil dari SUMIF tadi menjadi satu angka total.
• Jadi hasil akhirnya adalah total penjualan dari semua produk dalam daftar pengecualian.

4. SUM(F3:F19) - SUMPRODUCT(...)

• Total semua penjualan dikurangi total penjualan produk yang ada di daftar pengecualian.
• Hasilnya adalah total penjualan selain produk yang ada di daftar H3\:H5.

Hasil Perhitungan

Dengan rumus di atas, Excel otomatis menghitung total penjualan tanpa menyertakan Daihatsu Xenia 2020, Honda Jazz 2018, dan Suzuki Ertiga 2019.

Hasilnya adalah: Rp 2.590.000.000. Berikut penampakannya:

Artinya, jika total penjualan semua produk adalah sekian rupiah, maka setelah dikecualikan produk-produk tertentu, sisanya adalah Rp 2,59 miliar.

Manfaat Cara Menjumlah Data dengan Kriteria Pengecualian di Excel

Menggunakan teknik ini memiliki banyak manfaat:

1. Efisiensi Waktu

• Tidak perlu filter manual atau hapus data. Rumus langsung menghitung otomatis.

2. Dinamis

• Jika daftar produk yang dikecualikan berubah, rumus otomatis menyesuaikan hasilnya.

3. Akurat

• Mengurangi risiko salah hitung karena semua perhitungan dilakukan otomatis oleh Excel.

4. Fleksibel

• Bisa diterapkan pada berbagai kasus, misalnya laporan penjualan, absensi, keuangan, atau data proyek.

Penerapan Lain

Selain contoh penjualan di atas, cara menjumlah data dengan kriteria pengecualian di Excel bisa diterapkan untuk:

• Menghitung total pengeluaran tanpa memasukkan kategori tertentu (misalnya pengeluaran pribadi).
• Menjumlah nilai ujian siswa kecuali mata pelajaran tertentu.
• Menyusun laporan keuangan dengan pengecualian akun tertentu.
• Menghitung total jam kerja kecuali shift tertentu.

Kesimpulan

Teknik cara menjumlah data dengan kriteria pengecualian di Excel sangat berguna ketika kita ingin menghitung total data dengan mengabaikan beberapa item tertentu. Dengan menggunakan kombinasi SUM, SUMIF, dan SUMPRODUCT, kita bisa mendapatkan hasil yang akurat, otomatis, dan fleksibel.

Rumus yang digunakan adalah:

=SUM(F3:F19)-SUMPRODUCT(SUMIF(C3:C19;H3:H5;F3:F19))

Rumus ini bekerja dengan cara menghitung total keseluruhan, lalu mengurangi total data yang sesuai dengan kriteria pengecualian.

Dengan memahami rumus ini, Anda tidak hanya bisa menghemat waktu, tetapi juga membuat laporan yang lebih rapi dan profesional. Jadi, mulai sekarang, jangan ragu untuk mencoba cara menjumlah data dengan kriteria pengecualian di Excel pada file pekerjaan Anda.

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 Menjumlah Data dengan Kriteria Pengecualian di Microsoft 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!