Rumus SUMIFS dan COUNTIFS 2 Kriteria di Excel untuk Rekap Kas Kelas per Bulan
Untuk menjumlahkan data dengan 2 kriteria di Excel, pakai SUMIFS: =SUMIFS(kolom_jumlah, kolom_kriteria1, kriteria1, kolom_kriteria2, kriteria2). Contohnya, total iuran kas yang dibayar Ahmad saja: =SUMIFS(D:D, B:B, "Ahmad Fauzi", C:C, "Masuk"). Untuk menghitung berapa kali datanya muncul, pakai COUNTIFS dengan kriteria yang sama. Di artikel ini keduanya dipakai untuk rekap kas kelas per siswa dan per bulan.
Dicek terakhir: 15 November 2026 oleh Arfan Putra. Rumus template dihitung ulang dengan mesin rumus dan dicocokkan dengan hitungan manual untuk 522 transaksi contoh (30 siswa, Juli–Desember 2026), termasuk transaksi pada tanggal 1 dan tanggal 31. Ada 332 angka yang dicek, dan semuanya cocok. Aturan fungsi dicocokkan dengan halaman Microsoft Support di bagian Sumber.
SUMIF dan SUMIFS: Apa Bedanya?
- SUMIF hanya menerima 1 kriteria:
=SUMIF(kolom_kriteria, kriteria, kolom_jumlah). Kolom yang dijumlahkan ada di belakang. - SUMIFS menerima 1 kriteria atau lebih. Menurut Microsoft, sampai 127 pasangan rentang dan kriteria. Kolom yang dijumlahkan ada di depan:
=SUMIFS(kolom_jumlah, kolom_kriteria1, kriteria1, kolom_kriteria2, kriteria2, ...).
Urutan argumen yang terbalik inilah yang paling sering bikin salah. Microsoft sendiri menyebutnya sumber masalah yang umum. Supaya tidak bingung, biasakan pakai SUMIFS saja, bahkan untuk 1 kriteria.
Langkah 1: Siapkan Buku Kas dalam Bentuk Daftar
SUMIFS bekerja paling baik kalau datanya berbentuk daftar: satu baris untuk satu transaksi. Di template, sheet Transaksi berisi kolom berikut (contoh isinya):
| A Tanggal | B Nama siswa | C Jenis | D Jumlah | E Keterangan |
|---|---|---|---|---|
| 03/08/2026 | Ahmad Fauzi | Masuk | Rp 5.000 | Iuran kas |
| 12/08/2026 | (pengeluaran) | Keluar | Rp 45.000 | Beli spidol dan penghapus |
Kolom Jenis memakai daftar pilihan (Masuk/Keluar), supaya tidak ada yang mengetik "masuk ", "Msk", atau "Pemasukan". Kriteria SUMIFS harus sama dengan isi sel. Beda satu huruf atau ada spasi berlebih saja, datanya tidak ikut terhitung.
Langkah 2: SUMIFS dengan 2 Kriteria (Total per Siswa)
Di sheet Rekap Siswa, nama siswa ada di kolom B mulai baris 6. Total setoran Ahmad (kolom J):
=SUMIFS(Transaksi!$D$5:$D$1504,Transaksi!$B$5:$B$1504,$B6,Transaksi!$C$5:$C$1504,"Masuk")
- Kriteria 1: kolom Nama sama dengan nama di B6. Kriteria ini diambil dari sel, bukan diketik, sehingga rumusnya cukup ditarik ke bawah untuk 30 siswa.
- Kriteria 2: kolom Jenis berisi "Masuk", sehingga pengeluaran kas tidak ikut terjumlah.
- Tanda
$mengunci rentang supaya tidak bergeser saat rumus disalin.
Semua rentang harus sama panjang (di sini baris 5 sampai 1504). Microsoft menyebut rentang kriteria wajib punya jumlah baris dan kolom yang sama dengan rentang jumlah.
Langkah 3: COUNTIFS untuk Menghitung Berapa Kali Bayar
Kolom C di Rekap Siswa menghitung berapa kali siswa menyetor:
=COUNTIFS(Transaksi!$B$5:$B$1504,$B6,Transaksi!$C$5:$C$1504,"Masuk")
Bentuknya sama dengan SUMIFS, hanya tanpa kolom jumlah di depan. COUNTIFS menghitung baris yang memenuhi semua kriteria sekaligus.
Langkah 4: Tambah Kriteria Tanggal untuk Rekap per Bulan
Di baris 5 (D5:I5) Rekap Siswa ada tanggal 1 setiap bulan: 1 Juli 2026, 1 Agustus 2026, dan seterusnya. Formatnya diatur supaya tampil sebagai "Jul 2026". Setoran Ahmad bulan Juli di D6:
=SUMIFS(Transaksi!$D$5:$D$1504,Transaksi!$B$5:$B$1504,$B6,Transaksi!$C$5:$C$1504,"Masuk",Transaksi!$A$5:$A$1504,">="&D$5,Transaksi!$A$5:$A$1504,"<"&DATE(YEAR(D$5),MONTH(D$5)+1,1))
Kolom tanggal dipakai dua kali: lebih dari atau sama dengan tanggal 1 bulan itu, dan kurang dari tanggal 1 bulan berikutnya. Cara ini tetap benar untuk bulan yang berakhir di tanggal 30, 31, atau 28/29 (Februari), dan transaksi tanggal 31 tidak terlewat.
Perhatikan penulisan ">="&D$5. Operator perbandingan ditulis sebagai teks di dalam tanda kutip, lalu disambung dengan sel tanggal memakai &. Kalau ditulis ">=D5", Excel mencari teks "D5", bukan tanggal di sel D5, sehingga hasilnya 0.
Tanda $ pada $B6 mengunci kolom nama, dan pada D$5 mengunci baris bulan. Dengan begitu rumus bisa disalin ke kanan dan ke bawah sekaligus untuk 6 bulan × 30 siswa.
Langkah 5: Kurang dari Target dan Status
Target iuran per bulan diisi di sel kuning C3 (contoh Rp 20.000). Kekurangan di kolom K:
=MAX(0,$C$3*COUNT($D$5:$I$5)-J6)
COUNT($D$5:$I$5) menghitung jumlah bulan di baris judul (6 bulan). MAX(0, ...) membuat siswa yang membayar lebih tetap tercatat 0, bukan minus. Status di kolom L: =IF(K6=0,"Lunas","Kurang"). Di bawah tabel, COUNTIF menghitung jumlah siswa yang masih berstatus Kurang. Di data contoh ada 7 siswa.
Langkah 6: Rekap Bulanan Masuk, Keluar, dan Saldo
Sheet Rekap Bulanan memakai kriteria Jenis + rentang tanggal, tanpa kriteria nama:
B4 =SUMIFS(Transaksi!$D$5:$D$1504,Transaksi!$C$5:$C$1504,"Masuk",Transaksi!$A$5:$A$1504,">="&$A4,Transaksi!$A$5:$A$1504,"<"&DATE(YEAR($A4),MONTH($A4)+1,1))
C4 (sama, kriteria "Keluar")
D4 =0+B4-C4 (bulan berikutnya: =D4+B5-C5, dan seterusnya)
E4 =COUNTIFS(Transaksi!$C$5:$C$1504,"Masuk",Transaksi!$A$5:$A$1504,">="&$A4,Transaksi!$A$5:$A$1504,"<"&DATE(YEAR($A4),MONTH($A4)+1,1))
Saldo akhir bulan adalah saldo bulan lalu ditambah uang masuk, lalu dikurangi uang keluar. Kalau kas sudah punya saldo awal sebelum Juli, ganti angka 0 di D4 dengan saldo awal itu. Nominal Keluar ini bisa langsung dibuatkan kwitansi lengkap dengan terbilangnya. Caranya ada di artikel rumus Excel terbilang rupiah tanpa add-in.
Catatan penulisan rumus: rumus di artikel ini memakai koma (,) sebagai pemisah. Kalau Excel menolak rumusnya, ganti koma dengan titik koma (;) sesuai pengaturan wilayah di Windows.
Kalau Hasilnya Error
- Hasilnya 0 padahal datanya ada. Penyebab paling sering adalah teks kriteria tidak sama persis. Contohnya ada spasi di belakang nama ("Ahmad Fauzi "), atau ejaan berbeda ("Muh." dan "Muhammad"). Salin nama dari Rekap Siswa ke sheet Transaksi, jangan diketik ulang. Untuk Jenis, pakai daftar pilihan yang sudah ada.
- #VALUE! Rentang jumlah dan rentang kriteria tidak sama panjang, misalnya
D5:D1504denganB5:B1000. Samakan semua barisnya. - Total bulanan 0 atau terlalu kecil. Tanggal di kolom A tersimpan sebagai teks, biasanya karena ditempel dari WhatsApp atau dari file CSV. Cek dengan
=ISNUMBER(A5). Kalau hasilnya FALSE, ketik ulang tanggalnya dengan format yang sesuai pengaturan Windows, atau ubah dengan fitur Text to Columns (Teks ke Kolom). - Hasilnya 0 atau angkanya tidak masuk akal setelah mengubah SUMIF menjadi SUMIFS. Urutan argumennya masih memakai urutan SUMIF. Di SUMIFS, rentang yang dijumlahkan selalu ada di posisi pertama.
- Rumus
">=D5"menghasilkan 0. Referensi sel ikut masuk ke dalam tanda kutip. Tulis operatornya di dalam kutip, lalu sambung dengan sel:">="&D5. - Transaksi baru tidak terhitung. Data diketik di bawah baris 1504, di luar rentang rumus. Lanjutkan di baris kosong yang tersedia, atau perpanjang rentang di semua rumus sekaligus dengan Find & Replace (Ctrl+H): ganti
$1504menjadi angka baris baru.
Sumber
- SUMIFS function dan SUMIF function, Microsoft Support
- COUNTIFS function, Microsoft Support
- DATE function, COUNT function, dan MAX function, Microsoft Support
