Menjumlahkan Mengikut Kriteria dengan SUMIF

Katakan nama wilayah berada dalam A2:A10 dan jumlah jualan berada dalam B2:B10. Untuk menjumlahkan jualan bagi wilayah Timur sahaja, uji lajur A untuk mencari perkataan Timur dan tambahkan jumlah sepadan daripada lajur B.

Hamparan memeriksa setiap sel wilayah. Di mana-mana sel yang mengandungi Timur, hamparan mengambil jumlah bagi baris tersebut dan menambahkannya kepada jumlah terkumpul. Wilayah lain diabaikan.

Apabila syarat anda ialah perkataan, apit perkataan itu dengan tanda petikan berganda: "East". Tanpa tanda petikan, hamparan akan menganggap Timur sebagai nama atau label dan berkemungkinan mengembalikan sifar.

Padanan tidak peka huruf besar dan kecil, jadi "east" dan "East" sepadan dengan baris yang sama. Pastikan sahaja ejaannya sepadan tepat dengan data, termasuk tanpa ruang tambahan.

Menulis syarat secara manual tidak menjadi masalah, tetapi lebih kemas jika anda meletakkannya dalam sel. Jika D1 mengandungi perkataan Timur, rujuk D1 dan bukannya menulis teks itu.

Kini anda boleh menukar wilayah dalam D1 dan jumlah anda akan dikemas kini serta-merta. Ini menjadikan formula boleh digunakan semula dan menukar satu sel menjadi suis kawalan ringkas untuk laporan anda.

Kriteria tidak semestinya teks. Untuk menjumlahkan setiap jumlah yang sama tepat dengan 500, gunakan sahaja nombor itu sebagai syarat.

Apabila lajur ujian dan lajur jumlah adalah sama, anda juga boleh meninggalkan argumen ketiga. Di sini, lajur B diuji dan dijumlahkan, jadi hanya baris yang nilai B-nya ialah 500 akan ditambahkan.

SUMIF boleh melakukan lebih daripada padanan tepat. Letakkan operator perbandingan di dalam tanda petikan untuk menjumlahkan julat nombor:

• ">100" lebih besar daripada 100
• "<=50" 50 atau kurang
• "<>0" tidak sama dengan sifar

Formula ini menjumlahkan setiap jualan yang melebihi 100. Operator dan nombor itu berada dalam satu pasangan tanda petikan.

Bagaimana jika nilai ambang berada dalam sel, katakan D1 mengandungi 100, dan anda mahukan jumlah yang lebih besar daripadanya? Anda tidak boleh menulis ">D1" kerana itu akan menjadi teks harfiah D1. Sebaliknya, gabungkan operator dengan sel menggunakan ampersan.

Ampersan menggabungkan teks ">" dengan nilai dalam D1, lalu membina kriteria ">100" secara dinamik.

julat dan julat_jumlah hendaklah mempunyai ketinggian yang sama dan bermula pada baris yang sama. Jika nama wilayah berada dalam A2:A10, jumlah mesti berada dalam B2:B10, bukan B3:B11.

Apabila kedua-duanya tidak sejajar, hamparan akan memadankan wilayah yang salah dengan jumlah yang salah dan jumlah anda menjadi salah tanpa sebarang amaran. Sentiasa semak semula bahawa kedua-dua julat meliputi baris yang sama tepat.

Bayangkan helaian perbelanjaan dengan kategori dalam lajur A dan kos dalam lajur B. Anda mahu jadual ringkasan kecil yang menunjukkan jumlah bagi Perjalanan, Makanan dan Pejabat.

Letakkan setiap nama kategori dalam sel D2, D3 dan D4, kemudian tulis satu SUMIF yang merujuk sel nama kategori. Salin formula itu ke bawah dan setiap baris akan menjumlahkan kategorinya sendiri. Satu corak formula, tiga jumlah kecil serta-merta.

Perhatikan bahawa contoh terakhir menggunakan A:A dan B:B, iaitu lajur penuh. Ini berguna apabila baris terus ditambah kerana anda tidak perlu memanjangkan julat.

Pastikan sahaja teks baris pengepala tidak sepadan dengan kriteria secara tidak sengaja, dan pastikan kedua-dua lajur sejajar. Dalam helaian yang sangat besar, julat terhad seperti A2:A1000 boleh dikira sedikit lebih pantas.

Kini anda tahu cara menjumlahkan nombor secara bersyarat dengan SUMIF. Perkara penting:

• Susunannya ialah julat, kriteria, julat_jumlah.
• Kriteria teks memerlukan tanda petikan, dan padanan tidak membezakan huruf besar dan kecil.
• Operator seperti ">100" berada di dalam tanda petikan; gabungkan operator dengan sel menggunakan &.
• Pastikan julat dan julat_jumlah sejajar pada baris yang sama.

Seterusnya, anda akan mengira baris sepadan dengan COUNTIF.