Google sheets sum jika sel lain berisi teks

Dalam contoh ini, tujuannya adalah menjumlahkan Jumlah di kolom C menurut negara bagian menggunakan kode dua huruf di kolom E. Perhatikan negara bagian disingkat, "CA" adalah California, "FL" adalah Florida, "TX" adalah Texas, dan "WA" adalah Washington. Tantangan dalam hal ini adalah bahwa singkatan negara disematkan dalam string teks. Ini berarti kita perlu membuat kriteria yang melakukan pencocokan tipe "contains". Untuk mengatasi masalah ini, Anda dapat menggunakan fungsi SUMIF atau fungsi SUMIFS dengan wildcard. Jika Anda memerlukan rumus peka huruf besar/kecil, Anda dapat menggunakan fungsi SUMPRODUCT dengan fungsi FIND. Ketiga pendekatan tersebut dijelaskan di bawah ini

Catatan. contoh ini menyatukan sejumlah ide, yang membuatnya lebih maju. Jika Anda merasa contoh ini menantang, rumus ini adalah pengantar yang lebih sederhana untuk ide yang sama, tanpa menggunakan teks dari sel lain sebagai kriteria

Data dalam Tabel Excel

Untuk kenyamanan, semua data ada di Tabel Excel bernama data dalam rentang B5. C16. Ini memungkinkan kita untuk menggunakan referensi terstruktur seperti data[Jumlah] untuk merujuk ke kolom Jumlah tanpa menggunakan referensi absolut. Ini juga berarti rumus akan terus berfungsi dengan benar jika data baru ditambahkan ke tabel

Video. Apa itu Tabel Excel?

Wildcard

Fungsi Excel seperti SUMIF dan SUMIFS mendukung karakter wildcard "?" . Wildcard memungkinkan Anda membuat kriteria seperti "dimulai dengan", "diakhiri dengan", "berisi 3 karakter" dan seterusnya. Tabel di bawah menunjukkan beberapa kemungkinan konfigurasi wildcard. Untuk data dalam masalah ini, kami ingin menggunakan pola "Sel yang berisi teks dalam A1", yang menggunakan dua tanda bintang (*) yang digabungkan ke referensi sel seperti "*"&A1&"*"

Perhatikan bahwa wildcard diapit tanda kutip ganda ("") saat muncul di kriteria. Perhatikan juga bahwa referensi sel tidak diapit dengan tanda kutip ganda. Sebagai gantinya, mereka digabungkan menjadi wildcard

solusi SUMIFS

Salah satu cara untuk mengatasi masalah ini adalah dengan fungsi SUMIFS. SUMIFS dapat menangani banyak kriteria, dan sintaks umum untuk satu kondisi terlihat seperti ini

=SUMIFS(sum_range,range1,criteria1)

Perhatikan bahwa rentang penjumlahan selalu didahulukan dalam fungsi SUMIFS. Mengikuti pola ini, rumus di sel F5 dari lembar kerja yang ditampilkan terlihat seperti ini

=SUMIFS(data[Amount],data[Location],"*, "&E5&" *")

Kisaran jumlah adalah data[Jumlah] dan rentang kriteria adalah data[Lokasi]. Kriteria itu sendiri adalah bagian rumit dari formula ini

"*, "&E5&" *" // criteria includes comma and space

Perhatikan teks dan wildcard (*) keduanya diapit tanda kutip ganda ("") dan kami menyertakan koma dan spasi (",") di sebelah kiri dan spasi (" ") di sebelah kanan untuk mencegah kecocokan yang salah. Karakter tambahan ini membuat kriteria ini lebih spesifik, sehingga kami tidak secara tidak sengaja mencocokkan "CA" dengan kata lain, seperti "cat" atau "FL" dengan "flood". Ini penting sebagian karena fungsi SUMIFS dan SUMIF tidak peka huruf besar-kecil. Perhatikan juga bahwa referensi sel E5 tidak diapit dengan tanda kutip ganda. Sebagai gantinya, ini digabungkan dengan karakter pengganti di kedua sisi. Kami tidak dapat menggunakan kriteria seperti "*, E5 *" karena itu hanya cocok dengan teks literal ", E5"

Saat Excel mengevaluasi rumus SUMIF, Excel akan mengambil nilai dari E5 ("CA"), melakukan penggabungan, dan menyusun kriteria menjadi satu string teks seperti ini

=SUMIFS(data[Amount],data[Location],"*, CA *")

Saat rumus dimasukkan di sel F5, rumus tersebut mengembalikan $600, jumlah untuk jumlah di California ("CA"). Saat rumus disalin, ia mengembalikan jumlah jumlah untuk setiap negara bagian di kolom E.  

solusi SUMIF

Masalah ini juga dapat diselesaikan dengan fungsi SUMIF, di mana rumusnya setara

=SUMIF(data[Location],"*, "&E5&" *",data[Amount])

Perhatikan bahwa sum_range datang terakhir dalam fungsi SUMIF. Namun, kriterianya identik dengan yang kami gunakan di SUMIFS di atas. Hasil yang dikembalikan oleh SUMIF adalah sama, $600 di sel F5 untuk penjumlahan di California

Solusi peka huruf besar-kecil

Seperti disebutkan di atas, fungsi SUMIF dan SUMIFS tidak peka huruf besar-kecil. Jika Anda memerlukan solusi peka huruf besar kecil, Anda dapat menggunakan rumus berdasarkan fungsi SUMPRODUCT dan fungsi FIND seperti ini

=SUMPRODUCT(--ISNUMBER(FIND(E5,data[Location]))*data[Amount])

Di dalam SUMPRODUCT, sisi kiri ekspresi menguji status dengan ISNUMBER dan FIND

--ISNUMBER(FIND(E5,data[Location]))

Fungsi FIND peka huruf besar-kecil secara default dan mengembalikan posisi find_text sebagai angka saat ditemukan, dan #VALUE. kesalahan bila tidak ditemukan. Kita tidak perlu menggunakan karakter wildcard seperti (*) karena FIND secara otomatis melakukan pencarian tipe "contains" untuk substring. Karena ada 12 nilai dalam data[Location], FIND mengembalikan array berisi 12 hasil seperti ini

{#VALUE!;14;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;12;10;#VALUE!;#VALUE!}

Perhatikan bahwa FIND mengembalikan angka untuk baris kedua, kesembilan, dan kesepuluh dalam data. Ini adalah baris dengan status "CA". Fungsi ISNUMBER mengubah hasil dari FIND menjadi nilai BENAR dan SALAH, dan negatif ganda (--) mengubah nilai BENAR dan SALAH menjadi 1s dan 0s. Di dalam SUMPRODUCT yang sekarang kita miliki

=SUMPRODUCT({0;1;0;0;0;0;0;0;1;1;0;0}*data[Amount])

Catatan. secara teknis, negatif ganda (--) tidak diperlukan dalam rumus ini, karena mengalikan nilai BENAR dan SALAH dengan nilai numerik dalam data[Jumlah] akan secara otomatis mengonversi nilai BENAR dan SALAH menjadi 1 dan 0. Namun, double negative tidak membahayakan dan membuat rumus sedikit lebih mudah dipahami, karena menandakan operasi Boolean

Video. Operasi Boolean dalam rumus array

Saat dua larik dikalikan bersama, angka nol di larik pertama bertindak seperti filter untuk membatalkan jumlah untuk semua negara bagian kecuali "CA"

=SUMPRODUCT({0;225;0;0;0;0;0;0;200;175;0;0})

Dengan hanya satu larik untuk diproses, SUMPRODUCT menjumlahkan nilai dalam larik dan mengembalikan 600 sebagai hasil akhir. Saat rumus disalin, kami mendapatkan jumlah jumlah yang peka terhadap huruf besar-kecil untuk setiap singkatan negara bagian di kolom E

Satu hal yang mungkin Anda perhatikan dalam rumus ini adalah bahwa kami tidak menggabungkan koma dan spasi ke nilai sel E5, karena huruf besar "CA" tidak mungkin cocok dengan teks yang salah. Namun, kami dapat menambahkan langkah ini untuk membuat pencarian lebih spesifik

=SUMIFS(data[Amount],data[Location],"*, "&E5&" *")

Seperti disebutkan di atas, tidak perlu menggunakan wildcard dalam hal ini karena FIND secara otomatis mencari find_text sebagai substring yang mungkin muncul di manapun dalam teks. Untuk penjelasan lebih rinci tentang FIND + ISNUMBER lihat artikel ini

Catatan. Di Excel 365, Anda bisa mengganti SUMPRODUCT dengan fungsi SUM. Untuk membaca lebih lanjut tentang ini, lihat Mengapa SUMPRODUCT?

Bagaimana cara menjumlahkan sel berdasarkan teks di sel lain Google Sheets?

Bagaimana Anda menjumlahkan nilai jika sel berisi teks?

Bagaimana cara menjumlahkan sel berdasarkan nilai di sel lain?

Bagaimana Anda menggunakan Sumif dengan kriteria teks?