Salah satu pertanyaan umum di Message Board adalah bagaimana menggunakan fungsi SumIf dengan dua kondisi berbeda. Sayangnya, jawabannya adalah SumIf tidak dapat menangani dua kondisi berbeda.
Untuk melakukan dua kondisi, Anda harus menggunakan rumus array yang agak rumit. Add-in panduan penjumlahan bersyarat memungkinkan Anda memasukkan rumus rumit ini dengan mudah.
Berikut adalah lembar kerja Excel dengan kolom untuk produk, perwakilan penjualan dan penjualan. Data ada di sel A2: C29.
Jika Anda ingin menjumlahkan penjualan, fungsi SUM () sederhana akan berfungsi. =SUM(C2:C29).
Banyak Exceller menemukan fungsi SumIf. Dengan menggunakan fungsi ini, cukup mudah untuk mengetahui total penjualan produk ABC.=SUMIF(A2:A29,E2,C2:C29)
Juga mudah untuk mengetahui total penjualan yang dilakukan oleh perwakilan penjualan Joe =SUMIF(B2:B29,E2,C2:C29).
Anda kemudian akan berasumsi bahwa adalah mungkin untuk mengetahui total penjualan produk ABC yang dibuat oleh Joe. Namun, tidak ada cara untuk melakukan ini dengan fungsi SumIf. Ternyata Anda perlu menggunakan array atau rumus CSE yang cukup kompleks.
Mari kita hadapi itu - rumus Sum adalah Excel 101. Rumus SumIf tidak jauh ketinggalan dalam kompleksitas. Namun, rumus CSE untuk menghitung total penjualan ABC yang dilakukan oleh Joe sudah cukup membuat kepalaku malah pusing.
Kabar baiknya - Microsoft menawarkan Wisaya Jumlah Bersyarat yang bahkan memungkinkan seorang pemula untuk memasukkan rumus bersyarat yang kompleks berdasarkan 1, 2 atau lebih kondisi. Panduan Jumlah Bersyarat adalah add-in. Untuk menambahkan fungsionalitas ini ke Excel, buka menu Alat dan pilih Add-In. Dalam kotak dialog Add-in, pilih kotak centang di samping Panduan Jumlah Bersyarat dan pilih OK. Mungkin Anda memerlukan CD penginstalan saat ini, karena Microsoft tidak menyertakan wizard di penginstalan default.
Setelah add-in berhasil diaktifkan, akan ada pilihan Conditonal Sum… di dekat bagian bawah menu Tools.
Pilih satu sel dalam dataset Anda dan pilih Tools - Conditional Sum. Dengan asumsi bahwa data Anda diformat dengan baik dengan satu baris judul, Excel akan menebak dengan tepat kisaran data Anda. Pilih Berikutnya.
Pada langkah 2, pilih kolom yang akan dijumlahkan. Dalam kasus ini, wizard sudah menebak Anda ingin menjumlahkan kolom numerik pertama (dan satu-satunya) - Penjualan. Di tengah kotak dialog ada tiga kontrol dropdown. Ini kebetulan benar untuk kondisi pertama - Produk sama dengan ABC, jadi pilih tombol Add Condition.
Kemudian Anda bisa menambahkan ketentuan kedua Anda. Dalam kasus ini, Anda ingin menetapkan bahwa Sales Rep adalah Joe. Pilih panah untuk dropdown pertama. Excel menawarkan daftar alfabet dari nama kolom yang tersedia. Pilih Sales Rep.
Dropdown tengah benar, tetapi untuk kelengkapan di sini, Anda dapat melihat bahwa Anda dapat memilih sama, kurang dari, lebih besar dari, kurang dari atau sama, lebih besar dari atau sama, atau tidak sama.
Dari dropdown ketiga, pilih Joe.
Pilih tombol Add Condition.
Anda sekarang siap untuk melanjutkan ke Langkah 3. Tekan tombol Berikutnya.
Pada langkah 3, Anda memiliki dua pilihan. Pada pilihan pertama, Wizard akan memasukkan satu rumus dengan nilai "ABC" dan "Joe" yang di-hardcode ke dalam rumus. Ini akan memberi Anda jawabannya, tetapi tidak akan ada kesempatan untuk mengubah rumus dengan mudah. Dengan pilihan kedua, Excel akan menyiapkan sel baru dengan nilai "ABC" dan sel baru dengan nilai "Joe". Sel ketiga akan berisi rumus yang melakukan penjumlahan bersyarat berdasarkan kedua nilai tersebut. Dengan opsi ini, Anda dapat mengetikkan nilai baru ke dalam sel untuk melihat total XYZ yang dijual oleh Adam.
Wizard kemudian akan menanyakan di mana Anda menginginkan nilai untuk ABC. Pilih sel dan pilih Berikutnya. Ulangi saat Wizard meminta Anda memilih sel untuk Joe dan rumusnya.
Saat Anda memilih Selesai di langkah terakhir, Excel akan membuat versi rumus CSE yang sedikit berbeda (tapi valid).
Rumus ini menghitung bahwa Joe menjual ABC senilai $ 33.338.
Jika Anda mengubah sel input produk dari ABC ke DEF, rumus akan menghitung ulang untuk memperlihatkan bahwa Joe menjual $ 24.478 DEF.
Panduan Penjumlahan Bersyarat menempatkan rumus kompleks dalam jangkauan semua pemilik Excel.
Informasi tambahan:Jika Anda ingin membuat tabel yang akan menunjukkan penjualan setiap produk oleh masing-masing perwakilan penjualan, ada beberapa "perhatian dan pemberian makan" khusus yang perlu Anda ketahui tentang formula ini. Ketik setiap perwakilan penjualan di bagian atas rentang. Ketik setiap produk di kolom kiri kisaran. Edit rumus yang disediakan oleh wizard. Pada gambar di bawah ini, rumusnya menunjuk produk di sel E6. Referensi ini benar-benar harus $ E6. Jika Anda meninggalkan referensi sebagai E6 dan menyalin rumus ke kolom G, rumus akan melihat F6 dan bukan E6 dan ini akan menjadi salah. Menambahkan tanda dolar sebelum E di E6 akan memastikan bahwa rumus selalu melihat produk di kolom E. Rumus tersebut juga mengarah ke perwakilan penjualan di sel F5. Referensi ini benar-benar harus F $ 5. Jika Anda meninggalkan referensi sebagai F5 dan menyalin ke baris 7,referensi F5 akan berubah menjadi F6 dan ini tidak benar. Menambahkan tanda dolar sebelum nomor baris akan mengunci nomor baris dan referensi akan selalu mengarah ke baris 5.
Dalam mode Edit (pilih sel dan tekan F2 untuk mengedit), ketik $ sebelum E. Ketik tanda dolar sebelum 5 di F5. Jangan tekan Enter dulu!
Rumus ini adalah jenis rumus khusus. Jika Anda menekan Enter, Anda akan mendapatkan 0, yang tidak benar.
Alih-alih mengetik Enter, tahan tombol Ctrl dan Shift saat Anda menekan Enter. Kombinasi ajaib dari C trl + S hift + E nter inilah mengapa saya menyebut rumus CSE ini.
Ada satu pertimbangan terakhir sebelum menyalin rumus ke tabel lainnya. Kecenderungan Anda untuk menyalin F6 dan menempelkannya ke F6: G8. Jika Anda mencoba ini, Excel akan menampilkan pesan "Anda Tidak Dapat Mengubah Bagian dari Larik" yang membingungkan. Excel mengeluh bahwa Anda tidak dapat menempelkan rumus CSE ke dalam rentang yang berisi rumus CSE asli.
Mudah untuk menyiasatinya. Salin F6. Tempel ke F7: F8.
Salin F6: F8. Tempel ke G6: G8. Anda akan memiliki tabel rumus CSE yang menunjukkan total berdasarkan dua kondisi.
