Rumus Excel untuk Kerja: IF, SUMIFS, XLOOKUP, dan Lainnya

Logika dan percabangan

Contoh ditulis dengan koma seperti lembar latihan; Excel berpengaturan wilayah Indonesia memakai titik koma: =IF(B2>=70;"Lulus";"Remedial"). Fungsi di luar daftar ini bisa dicoba di lab Kamus Rumus Spreadsheet (/lab/kamus-rumus-spreadsheet).

• IFLatih: Memilih hasil dengan IF

  Memilih satu dari dua hasil: hasil pertama bila syaratnya benar, hasil kedua bila salah. Teks hasil wajib di dalam tanda kutip.

  =IF(B2>=70,"Lulus","Remedial")

• IF bertingkatLatih: IF bertingkat

  IF di dalam IF untuk tiga hasil atau lebih. Syarat diperiksa berurutan dan berhenti di syarat pertama yang benar.

  =IF(B2<=1,10000,IF(B2<=5,25000,60000))

• IFSLatih: Banyak syarat dengan IFS

  Pasangan syarat–hasil berurutan; yang keluar hasil dari syarat pertama yang benar. Tanpa pasangan TRUE di akhir, nilai yang tidak memenuhi syarat mana pun menjadi #N/A.

  =IFS(B2<5,0,B2<17,15000,B2<60,35000,TRUE,20000)

• SWITCHLatih: Memilih berdasarkan nilai pasti: SWITCH

  Mencocokkan satu nilai dengan daftar nilai pasti dan mengembalikan pasangannya. Argumen terakhir tanpa pasangan menjadi hasil bawaan.

  =SWITCH(A2,"JKT","Jakarta","BDG","Bandung","SBY","Surabaya","Lainnya")

• AND / OR / NOTLatih: AND, OR, dan NOT

  AND benar bila semua syaratnya benar, OR benar bila salah satunya benar, NOT membalik hasilnya. Ketiganya dipakai sebagai syarat di dalam IF.

  =IF(AND(B2>=C2,C2<=3),"Disetujui","Ditolak")

Menghitung dan menjumlahkan dengan syarat

Kriteria perbandingan dengan isi sel lain ditulis sebagai teks yang disambung: ">="&F1, bukan >=F1.

• COUNTIFLatih: Menghitung dengan syarat: COUNTIF

  Menghitung sel yang memenuhi satu syarat.

  =COUNTIF(B2:B50,">="&F1)

• SUMIFLatih: SUMIF dan AVERAGEIF

  Menjumlahkan nilai pada baris yang memenuhi satu syarat. Urutannya: rentang syarat, kriteria, lalu rentang yang dijumlahkan.

  =SUMIF(A2:A40,"Transport",C2:C40)

• COUNTIFSLatih: Banyak syarat: COUNTIFS dan SUMIFS

  Menghitung baris yang memenuhi semua pasangan rentang–kriteria sekaligus.

  =COUNTIFS(B2:B100,"Jak*",C2:C100,">1000000")

• SUMIFSLatih: Banyak syarat: COUNTIFS dan SUMIFS

  SUMIF untuk banyak syarat, tetapi rentang yang dijumlahkan ditulis paling depan. Rentang tanggal ditulis sebagai dua syarat pada kolom yang sama.

  =SUMIFS(C2:C100,B2:B100,"Bandung",A2:A100,">="&DATE(2026,3,1),A2:A100,"<"&DATE(2026,4,1))

• AVERAGEIFS / MAXIFS / MINIFSLatih: Rata-rata dan nilai ekstrem bersyarat: AVERAGEIFS, MAXIFS, MINIFS

  Rata-rata, nilai terbesar, dan nilai terkecil dari baris yang memenuhi beberapa syarat. Susunan argumennya sama dengan SUMIFS.

  =MAXIFS(D2:D60,B2:B60,"Medan",C2:C60,">=6")

• Wildcard "*" dan "?"Latih: Mencari dengan wildcard

  Di kriteria COUNTIF, SUMIF, dan VLOOKUP, tanda * mewakili berapa pun karakter dan ? tepat satu karakter.

  =COUNTIF($J$2:$J$40,"Toko ?")

Mencari data dan menangkap #N/A

• VLOOKUP(…, FALSE)Latih: VLOOKUP dengan pencocokan persis

  Mencari kode di kolom pertama tabel lalu mengambil nilai dari kolom ke-n di kanannya. FALSE berarti harus sama persis; tanpa FALSE, Excel memakai pencocokan perkiraan.

  =VLOOKUP(A2,$J$2:$L$50,3,FALSE)

• VLOOKUP(…, TRUE)Latih: VLOOKUP pencocokan perkiraan untuk tabel bertingkat

  Pencocokan perkiraan untuk tabel batas bawah — bonus, diskon, predikat: diambil baris dengan batas terbesar yang tidak melebihi nilainya. Kolom pertama wajib urut naik.

  =VLOOKUP(B2,$J$2:$K$4,2,TRUE)

• XLOOKUPLatih: XLOOKUP

  Mencari di satu kolom dan mengambil dari kolom mana pun, termasuk yang di sebelah kiri. Tidak perlu menghitung nomor kolom.

  =XLOOKUP(A2,$K$2:$K$30,$J$2:$J$30)

• XLOOKUP: if_not_found dan match_modeLatih: XLOOKUP: if_not_found dan match_mode

  Argumen keempat adalah hasil bila nilai tidak ditemukan. Argumen kelima memilih cara mencocokkan: 0 persis, -1 persis atau lebih kecil berikutnya, 1 persis atau lebih besar berikutnya, 2 wildcard.

  =XLOOKUP(A2,$J$2:$J$4,$K$2:$K$4,"Di luar golongan",1)

• INDEX + MATCHLatih: INDEX dan MATCH, satu dan dua arah

  MATCH(…, 0) mencari posisi; INDEX mengambil nilai di posisi itu. Dua MATCH — satu untuk baris, satu untuk kolom — membaca tabel dua arah.

  =INDEX($B$2:$D$4,MATCH("Minibus",$A$2:$A$4,0),MATCH("Mingguan",$B$1:$D$1,0))

• XLOOKUP(1, (…)*(…), …)Latih: Mencari dengan dua kriteria

  Mencari dengan dua kriteria: perkalian dua perbandingan bernilai 1 hanya di baris yang cocok pada kedua kolom.

  =XLOOKUP(1,($J$2:$J$20=A2)*($K$2:$K$20=B2),$L$2:$L$20,"Kosong")

• IFERRORLatih: Menangkap kesalahan: IFERROR dan IFNA

  Mengganti galat apa pun — #N/A, #DIV/0!, #VALUE! — dengan nilai pilihan. Galat yang menandakan rumus salah ikut tersembunyi.

  =IFERROR(AVERAGEIF($A$2:$A$50,E2,$B$2:$B$50),0)

• IFNALatih: Membaca #N/A: IFNA, IFERROR, dan ISNA

  Hanya menangkap #N/A, yaitu nilai yang tidak ditemukan, sehingga galat lain tetap terlihat.

  =IFNA(VLOOKUP(A2,$H$2:$I$80,2,FALSE),"Bukan anggota")

Teks

• LEFT / RIGHT / MIDLatih: Memotong teks: LEFT, RIGHT, MID, LEN

  Mengambil sejumlah karakter dari kiri, dari kanan, atau mulai dari posisi tertentu.

  =MID(A2,4,4)

• LENLatih: Memotong teks: LEFT, RIGHT, MID, LEN

  Menghitung panjang teks, spasi ikut dihitung.

  =LEN(A2)

• TRIMLatih: Merapikan nama: TRIM, PROPER, UPPER, LOWER

  Membuang spasi di awal dan akhir teks serta memadatkan spasi ganda di tengah menjadi satu. Kode yang tampak sama tetapi gagal dicari VLOOKUP sering hanya butuh TRIM.

  =VLOOKUP(TRIM(A2),$F$2:$H$7,2,FALSE)

• PROPER / UPPER / LOWERLatih: Merapikan nama: TRIM, PROPER, UPPER, LOWER

  Menyeragamkan huruf: huruf awal tiap kata kapital, semua kapital, atau semua kecil.

  =PROPER(TRIM(A2))

• FIND / SEARCHLatih: Mencari posisi teks: FIND dan SEARCH

  Posisi sebuah teks di dalam teks lain. FIND membedakan huruf besar dan kecil; SEARCH tidak, dan menerima wildcard.

  =LEFT(A2,FIND(",",A2)-1)

• SUBSTITUTELatih: Mengganti teks: SUBSTITUTE

  Mengganti teks tertentu dengan teks lain. Argumen keempat memilih kemunculan ke berapa yang diganti.

  =SUBSTITUTE(A2,"-","")

• & / CONCAT / TEXTJOINLatih: Menggabungkan teks: &, CONCAT, dan TEXTJOIN

  Menggabungkan teks. TEXTJOIN menyisipkan pemisah dan melewati sel kosong bila argumen keduanya TRUE.

  =TEXTJOIN(", ",TRUE,A2:C2)

• VALUELatih: Membersihkan data impor: LEN, TRIM, dan VALUE

  Mengubah angka yang tersimpan sebagai teks, misalnya hasil impor, menjadi angka yang bisa dihitung.

  =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"Rp",""),".",""))

• TEXTBEFORE / TEXTAFTERLatih: Memisah dengan pemisah: TEXTBEFORE dan TEXTAFTER

  Mengambil teks sebelum atau sesudah sebuah pemisah. Angka negatif menghitung pemisahnya dari kanan.

  =TEXTAFTER(D2,"/",-1)

• TEXTLatih: Menampilkan tanggal sebagai teks: TEXT

  Mengubah angka atau tanggal menjadi teks berformat. Kode [$-421] menampilkan nama hari dan bulan dalam bahasa Indonesia.

  =TEXT(A2,"[$-421]dddd, d mmmm yyyy")

Tanggal

Tanggal di Excel adalah angka hari, jadi dua tanggal bisa dikurangkan dan tanggal bisa ditambah angka.

• DATE / YEAR / MONTH / DAYLatih: Membaca dan membentuk tanggal: DATE, YEAR, MONTH, DAY, EOMONTH

  Membentuk tanggal dari tahun, bulan, dan hari, atau mengambil bagian-bagiannya dari sebuah tanggal.

  =DATE(2027,1,1)

• EOMONTHLatih: Membaca dan membentuk tanggal: DATE, YEAR, MONTH, DAY, EOMONTH

  Tanggal akhir bulan, digeser sejumlah bulan. EOMONTH(…, -1)+1 memberi tanggal 1 di bulan yang sama.

  =EOMONTH(B2,-1)+1

• WEEKDAYLatih: Hari dalam minggu: WEEKDAY

  Nomor hari dalam minggu. Dengan jenis 2, Senin = 1 sampai Minggu = 7, sehingga >5 berarti akhir pekan.

  =IF(WEEKDAY(A2,2)>5,"Libur","Masuk")

• DATEDIFLatih: Masa kerja dan umur: DATEDIF satuan Y dan YM

  Selisih dua tanggal dalam tahun penuh ("Y") atau sisa bulan sesudahnya ("YM"). Tidak muncul di daftar saran fungsi, tetapi tetap bekerja bila diketik lengkap.

  =DATEDIF(C2,$H$1,"Y")&" tahun "&DATEDIF(C2,$H$1,"YM")&" bulan"

• NETWORKDAYSLatih: Selisih tanggal: DATEDIF dan NETWORKDAYS

  Jumlah hari kerja Senin–Jumat di antara dua tanggal, kedua ujungnya ikut dihitung. Argumen ketiga adalah daftar tanggal libur.

  =NETWORKDAYS(DATE(2026,8,1),DATE(2026,8,31))

• WORKDAYLatih: Hari kerja dengan libur: WORKDAY dan NETWORKDAYS

  Tanggal setelah sejumlah hari kerja, melewati akhir pekan dan daftar libur.

  =WORKDAY(A2,5,$H$2:$H$4)

Rekap, peringkat, dan pembulatan

• SUMPRODUCTLatih: SUMPRODUCT: bobot dan syarat ganda

  Mengalikan larik berpasangan lalu menjumlahkan hasilnya: total berbobot, atau total bersyarat tanpa kolom bantu bila syaratnya ikut dikalikan.

  =SUMPRODUCT((B2:B90="Gudang")*(MONTH(A2:A90)=5)*D2:D90)

• SUBTOTALLatih: SUBTOTAL

  Total yang tidak ikut menjumlahkan SUBTOTAL lain di rentangnya. Kode 9 menjumlahkan; 109 juga mengabaikan baris yang disembunyikan.

  =SUBTOTAL(9,C2:C30)

• RANK.EQLatih: Peringkat dengan RANK.EQ

  Peringkat sebuah nilai di daftarnya: 0 membuat nilai terbesar peringkat 1, angka 1 membuat nilai terkecil peringkat 1. Nilai kembar berbagi peringkat.

  =RANK.EQ(B2,$B$2:$B$6,0)

• LARGE / SMALLLatih: Top-N dengan LARGE dan SMALL

  Nilai terbesar atau terkecil ke-n; dipasangkan dengan INDEX dan MATCH untuk mengambil namanya.

  =LARGE($B$2:$B$40,D2)

• ROUND / ROUNDUP / MROUNDLatih: Pembulatan Rupiah: ROUND, ROUNDUP, dan MROUND

  Membulatkan Rupiah. Digit negatif membulatkan ke ratusan atau ribuan, ROUNDUP selalu ke atas, MROUND ke kelipatan terdekat.

  =MROUND(7260,500)

Larik dinamis

Untuk Excel 365 dan Excel 2021: satu rumus menghasilkan banyak nilai yang tumpah ke sel di bawah atau di kanannya.

• #SPILL!Latih: Rumus larik dan tumpahan (spill)

  Area tumpahan terhalang sel yang sudah berisi, walau isinya hanya spasi. Kosongkan sel itu atau pindahkan rumusnya.

• FILTERLatih: Menyaring baris dengan FILTER

  Mengambil baris yang memenuhi syarat. Dua syarat yang dikalikan berarti keduanya harus benar; argumen ketiga mengisi hasil bila tidak ada baris yang cocok.

  =FILTER(A2:C5,(B2:B5="Pagi")*(C2:C5>=70))

• UNIQUELatih: UNIQUE dan rekap per kategori

  Daftar nilai tanpa kembar. Sebagai kriteria COUNTIF atau SUMIF, satu rumus merekap semua kategori sekaligus.

  =SUMIF(A2:A7,UNIQUE(A2:A7),B2:B7)

• SORT / SORTBYLatih: Mengurutkan dengan SORT dan SORTBY

  SORT mengurutkan menurut nomor kolom (1 naik, -1 turun); SORTBY mengurutkan menurut kolom lain dan menerima beberapa kunci.

  =SORTBY(A2:A5,B2:B5,1,C2:C5,-1)

• SEQUENCELatih: Deret angka dan tanggal dengan SEQUENCE

  Deret angka dengan argumen: banyak baris, banyak kolom, nilai awal, dan langkah.

  =SEQUENCE(4,1,0,3)

• TAKE / DROP / CHOOSECOLS / VSTACKLatih: Memotong dan menggabung larik: TAKE, DROP, CHOOSECOLS, VSTACK

  Mengambil n baris pertama, membuang n baris, memilih kolom tertentu, atau menumpuk dua tabel menjadi satu.

  =TAKE(SORT(A2:C6,3,-1),2)

• LETLatih: LET dan XLOOKUP beberapa kolom

  Memberi nama pada bagian rumus supaya dihitung sekali dan rumus panjang tetap terbaca.

  =LET(n,B2:B7,rata,AVERAGE(n),SUM(--(n>rata)))