Rumus Excel SUMIF Bertingkat dan SUMIFS Bertingkat

Penulis
Advernesia
62
Rumus Excel SUMIF Bertingkat dan SUMIFS Bertingkat
Rumus Excel SUMIF Bertingkat dan SUMIFS Bertingkat

SUMIF dan SUMIFS adalah rumus excel yang mempunyai fungsi sama dengan rumus SUM dengan penambahan argumen kriteria data untuk melakukan perhitungan total data. Penggunaan rumus SUMIF dan SUMIFS sama dengan pengguna rumus SUM atau AutoSum. Berikut pengertian rumus excel SUMIF bertingkat dan Kriteria SUMIFS

Formula Deskripsi
SUM Menghitung total data tanpa menguji kondisi data yang dijumlahkan
SUMIF Menghitung total data dengan melakukan pengujian kriteria suatu data
SUMIFS Menghitung total data dengan melakukan lebih dari satu kriteria pengujian data

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

A. Rumus Fungsi SUM

Rumus Excel SUM adalah salah satu dari fungsi math dan trig. Rumus SUM sangat familiar karena rumus SUM juga sudah tersedia pada tombol perintah AutoSum pada ribbon Formula. Rumus SUM berfungsi untuk menghitung jumlah total data pada sel/range yang disorot tanpa melakukan pengujian data. Dalam matematika rumus SUM sama dengan Sigma (∑). Berikut cara membuat formula SUM.

=SUM(sel1,angka1,range1,...,selN)

Cara Menggunakan Fungsi SUM

Formula SUM dibuat dengan huruf kapital namun tidak case sensitive kemudian dilanjutkan dengan menambah sel, angka atau range yang akan dihitung total dalam tanda kurung.

Contoh Deskripsi
=SUM(1,2,3) Menghitung total data dengan nilai 1, 2 dan 3 yang menghasilkan angka 1+2+3=6
=SUM(nilai) Menghitung total data dari range yang bernama "nilai"
=SUM(A1:B9) Menghitung total data dari range A1:B9

B. Rumus Fungsi SUMIF

Rumus Excel SUMIF adalah rumus yang berfungsi untuk melakukan perintah SUM dengan melakukan pengujian data sehingga data yang memenuhi kriteria/argumen penguji yang dimasukkan dalam perhitungan total. Rumus SUMIF hanya dapat memuat satu kriteria argumen untuk melakukan pengujian data. Berikut sistematika formula SUMIF

=SUMIF(range,criteria,[sum_range])
  • range adalah range data utama yang akan diuji atau dihitung.
  • criteria adalah argumen yang akan diujikan pada setiap data sebelum dihitung total.
    • Jika kriteria argumen hanya berupa angka, maka data yang dihitung total hanya data yang bernilai sama dengan kriteria tersebut.
    • Argumen yang memuat huruf simbol matematika, dan operasi logika matematika ditulis dalam tanda "petik".
  • [sum_range] adalah variabel opsional, jika variabel ini didefinisikan nilai total dihitung dari range variabel ini (sum_range) dengan data yang diuji berasal dari range utama.

Berikut beberapa cara penulisan criteria pada Microsoft Excel.

  1. Peraturan penulisan criteria numerik dan logika

    Criteria Fungsi Contoh
    = sama dengan "=100"
    > lebih besar ">100"
    < lebih kecil "<100"
    <> tidak sama "<>100"
    >= lebih besar / sama ">=100"
    <= lebih kecil / sama "<=100"
    hanya <> Not Blank "<>"

  2. Peraturan criteria teks dan wildcard criteria

    Criteria Fungsi Contoh
    " " Mencocokkan teks identik "teks"
    "teks*" Mencocokkan teks dengan awalan "ad*"
    "*teks" Mencocokkan teks dengan akhiran "*esia"
    "?" Mencocokkan jumlah karakter teks "???"
    "*teks*" Mencocokkan teks yang memuat karakter "*verne*"
    "~*" Mencocokkan teks yang memuat tanda asterisk (*) "Lulus~*"
    "~?" Mencocokkan teks yang memuat tanda tanya (?) "Apa~?"

  3. Peraturan referensi sel dalam criteria

    Untuk referensi suatu sel ke dalam criteria dapat menggunakan operator &. Misalnya membuat criteria lebih dari sel A1, dapat dibuat criteria: ">"&A1.

Baca Juga: Cara membuat operasi matematika pada Excel

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

B1. Cara Penggunaan SUMIF Tanpa [sum_range]

Suatu toko buku akan menghitung total penjualan minggu pertama dimana setiap penjualan yang dihitung harus melebihi Rp100.000,00/hari. Berikut daftar penjualannya

 Hari  Penjualan
 Senin  Rp230.000,00
 Selasa  Rp420.000,00
 Rabu  Rp200.000,00
 Kamis  Rp87.000,00
 Jumat  Rp98.000,00
 Sabtu  Rp56.000,00
 Minggu  Rp200.000,00

Sehingga penyelesaiannya menggunakan formula berikut

=SUMIF(range_penjualan,">100000")

Cara Menggunakan SUMIF

Jadi, diperoleh total Rp1.050.000,00

B2. Cara Penggunaan SUMIF Lengkap

Pada contoh di atas akan dihitung jumlah buku yang terjual selama seminggu dari perolehan total penjualan yang melebihi Rp100.000,00/hari. Berikut laporan banyaknya penjualan buku

Hari Penjualan  Banyak Buku
Senin Rp230.000,00  18
Selasa Rp420.000,00  15
Rabu Rp200.000,00  12
Kamis Rp87.000,00  9
Jumat Rp98.000,00  6
Sabtu Rp56.000,00  3
Minggu Rp200.000,00  5

Sehingga dapat digunakan formula berikut

=SUMIF(range_penjualan,">100000",range_buku)

Cara Menggunakan SUMIF Lengkap

Jadi, banyaknya buku yang terjual dari total penjualan Rp 1.050.000,00 adalah 50 buah buku.

C. Rumus Fungsi SUMIFS

Rumus Excel SUMIFS adalah rumus yang berfungsi untuk menghitung total data dengan melakukan lebih dari satu pengujian kriteria data. Rumus ini sering disebut Rumus Excel SUMIF bertingkat. Berikut sistematika formula SUMIFS

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
  • sum_range adalah data yang akan dihitung total.
  • criteria_range1 adalah range yang akan diujikan dengan criteria1.
  • criteria_range2 adalag range yang akan diujikan dengan criteria2.
  • dan seterusnya.

PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.

Cara Penggunaan SUMIFS

Suatu perusahaan sepatu mempunyai 10 orang sales yang memasarkan sepatu di 2 provinsi yaitu Bali dan Jawa Timur. Sales-sales dikelompokan berdasarkan masa kerjanya yaitu senior (bekerja lebih dari 3 tahun), intermediate (1-3 tahun) dan junior (kurang dari 1 tahun). Hitunglah total penjualan sales senior di Bali dari tabel penjualan tahunan berikut.

 Nama  Masa Kerja  Provinsi  Penjualan
 Putu  1  Bali  123
 Hartono  2  Jawa Timur  348
 Widyastuti  3  Jawa Timur  506
 Ngurah  4  Bali  607
 Gusti  7  Jawa Timur  1206
 Hartanto  5  Bali  578
 Budiani  4  Bali  436
 Junaedi  1  Jawa Timur  98
 Angga  7  Bali  897
 Buda  6  Jawa Timur  2900

Untuk menyelesaikan contoh ini dapat digunakan formula

=SUMIFS(range_penjualan,range_masa_kerja,">3",range_provinsi,"Bali")

Cara Menggunakan SUMIFS

Jadi, total penjualan sales senior di Bali adalah 2518 sepatu.

Baca juga tutorial lainnya: Daftar Isi Tutorial Excel

Sekian artikel "Rumus Excel SUMIF Bertingkat dan Kriteria SUMIFS". Nantikan artikel menarik lainnya dan mohon kesediaannya untuk share dan juga menyukai Fans Page Advernesia. Terima kasih…

ARTIKEL TERKAITARTIKEL TERBARU

62 DISKUSI PEMBACA

  2. Selamat siang..

    Untuk rumus yang SUMIF Misalnya saya punya rumus
    =SUMIF(B6:B217;1;P6:P221)

    Apakah bisa kalau criteria di Angka 1 saya masukan criteria lain secara otomatis.

    Salam

    • Terima kasih kak Ifan Sihotang…
      Mohon maaf atas balasannya yang lambat,
      Misalnya =SUMIF(B6:B217;>C1;P6:P221)

      Disini kakak dapat mengganti nilai criteria dengan merubah nilai di sel C1
      Semoga bermanfaat 🙂

  3. Selamat malam,

    Terimakasih atas share ilmunya soal rumus excel, sangat membantu dan bermanfaat.

    Mau tanya dong soal sumifs, jika kriterianya lebih dari 2 gmn ya? Seperti contoh diatas gmn jika provinsinya ada 3 atau 4,(bali,jw timur, jw barat dst) pertanyaannya ingin mencari total penjualan sales senior di provinsi bali dan jw barat?

    Terimakasih

    • Terima kasih atas komentarnya kan Riyan.
      Mohon maaf atas keterlambatan respon.
      Saya sudah mencari-cari caranya, namun gabungan fungsi logikan AND tidak kunjung saya temukan untuk fungsi SUMIFS,
      Sebagai aternatif dapat menggunakan operasi aritmatika biasa,

      =SUMIFS(D2:D11,C2:C11,"Bali",B2:B11,">3")+SUMIFS(D2:D11,C2:C11,"Jawa Barat",B2:B11,">3")

      Semoga bermanfaat 🙂

  4. mau tanya, kl yg gmau dijumlah adalah total penjualan, dalam kasus di atas, pejualan dibagi menjadi 2 kolom lagi, yaiutu pejualan untung dan penjualan rugi, tetapi hasil yg diinginkan adalah jumlah total penjualannya, memakai formula sumifs

    • Kak hariyanto dapat menjumlahkan hasil penjualan untung dan penjualan rugi, bisa dibuat menjadi satu formula, misalnya

      =SUMIFS(penjualan untung,...)+SUMIFS(penjualan rugi,...)
      Formula dapat menyesuaikan dengan kriteria yang akan ditentukan

      Semoga bermanfaat kak 🙂

  5. contoh kasus bang gmna mencari 2
    ini soalnya
    KODE BARANG SPESIFIKASI CABANG JUMLAH JUAL
    LED-32-SUB 32 inch Surabaya 12
    LCD-29-SUB 29 inch Surabaya 20
    LED-50-JKT 50 inch Jakarta 8
    LCD-29-JKT 29 inch Jakarta 10
    LED-50-YOG 50 inch Yogyakarta 15

    soalnya : Mencari TV 29 Inch yg terjual di Jakarta dan Surabaya ??

    Mohon di jawab bg

    • Untuk mecari jumlah tv 29 inchi yang terjual di jakarta dan surabaya, dapat menggunakan formula
      =SUMIFS(range_jumlah,spesifikasi,"29 inch",cabang,"jakarta")+SUMIFS(range_jumlah,spesifikasi,"29 inch",cabang,"surabaya")

      Semoga bermanfaat kak Dwi Bagus 🙂

  6. bang mau tanya, kalau kasusnya (penjualan produk)
    sel A tanggal, Sel B kode, Sel C angka, kalau mencari hasil penjualan misal dari rentang waktu tertentu untuk satu buah produk gimana ya bang?
    tgl 1 jenis a 200rb
    jenis b 250rb
    jenis a 300rb
    tgl 2 jenis c 150rb
    jenis b 200rb
    jenis a 450rb
    tgl 3 jenis a 150rb
    jenis b 200rb
    jenis c 450rb
    dst smpai tgl 30, lah saya pengin mencari hasil penjualan jenis a dri tgl 10 sampai 15, itu gimana ya oh ya format sel a berupa dd/mm/yyyy terimakasih bang.

    • Mohon maaf atas keterlambatan balasannya kak...
      Website Advernesia sedang melakukan optimasi server saat itu

    • Agan dapat menggunakan rumus SUMIFS dengan pengujian data tanggal untuk melakukannya
      Pertama agan sebaiknya menentukan waktu tetap atas, misalnya di sel D1=10/04/2019
      Kedua agan sebaiknya menentukan waktu tetap bawah, misalnya di sel D2=15/04/2019

      =SUMIFS(range_angka,range_kode,"A",range_tanggal,">="&D1,range_tanggal,"<="&D2)

      Itu akan menjumlahkan nilai di kolom angka untuk jenis produk A tanggal 10 s/d 15 April 2019
      Semoga bermanfaat dan terima kasih banyak atas kesabarannya :)

  7. Kak, mau tanya kalo misalkan penggunaan sumif tetapi pemilihan kolomnya agar otomati mengikuti kriteria gimana?
    misalkan seperti data contoh diatas, tetapi kolom kesampingnya itu berdasarkan bulan.
    penjualan jan 2019, penjualan feb 2019 , penjualan mar 2019 , dst

    nah rumus sumif dibuat di sheet berbeda, dan akan dicopy ke samping juga
    jadi saya mau menampilkan penjualan area jawa timur per bulan menggunakan sumif dari data yg ada.
    mohon bantuannya kak.

    terima kasih sebelumnya kak.

    • Masalah ini dapat diselesaikan dengan bermacam solusi dengan Excel, salah satunya dengan Macros.
      Namun, disini saya mencoba memberikan solusi sederhana dengan pengalamatan sel semi absolut untuk range penjualan bulanan, dan absolut untuk range provinsi

      =SUMIFS(range_penjualan_bulanan,range_provinsi,"Bali")
      Misalnya
      Buat Penjualan bulan Januari=SUMIFS(sheet1!D$2:D$11,sheet1!$C$2:$C$11,"Jawa Timur")
      Buat Penjualan bulan Februari=SUMIFS(sheet1!E$2:E$11,sheet1!$C$2:$C$11,"Jawa Timur")

      Blok sel penjualan jan-feb, lalu lakukan autofill ke samping. Secara otomatis biasanya ms Excel dapat memahami data yang diautofill.

      Semoga dapat membantu kak Frans 🙂

  8. Bang mau tanya, kalau sumifs kriterianya adalah horizontal, bukan vertical itu bisa gak ya?

    Misalkan
    a b c d a b d
    X1 1 3 4 1 3 4 5
    X2 2 4 5 6 1 2 6

    berapa jumlah total dengan kriteria a?

  9. bang mau tanya..

    misal kita mau cari total liter penjualan suatu product suatu toko dalam 1 tahun, pakai rumus sumif nya seperti apa ya??

    contoh
    toko liter product
    A 2 SNACK
    B 3 ROTI

    dan seterusnya...

    terima kasih bang sebelumnya

    • Terima kasih kak Novian atas pertanyaannya, kakak dapat menambahkan nilai range tanggal pada kondisi yang akan dijumlahkan ... Hal ini hampir sama dengan dengan komentar Pak Bekti Aris Mawan di atas
      Semoga membantu 🙂

  10. TIME 14/02/2020 MINGGU SENIN TOTAL tanggal 30 31
    No. Description IN OUT IN OUT IN OUT

    1. CP 1 GALON FRESHLUX 100 0 100 0 =rumus = rumus

    kak tolong di bantu misal ada report buat kerjaan

    dengan format berikut

    tapi dengan item yang banyak serta pertanggal dan dalam colom dengan kriteria in dan out

    pertnyaaan nya tolong bantu untuk pernjumlahan in dan out dalam total dalam satu bulan jika padai sumif atau pakai apa kak mksh kak

    • Hai kak Rine, itu bisa pakai rumus IF dengan gabungan VLOOKUP atau HLOOKUP
      Semoga membantu 🙂

  13. Mau Tanya Kak Apakah rumus Sumifs bisa digunakan untuk data berikut :
    Kriteria
    1. 0% Tidak ada Produksi
    2. 0,01% s/d 75,00% Produksi Rendah
    3. 75,01% sd 90,00% Produksi Sedang
    4. Diatas 90,01% Produksi Tinggi.

    Mohon bantuannya kak untuk menjumlahkan menggunakan rumus SUMIFS apabila ada beberapa data yang ingin kita jumlahkan dengan kriteria No. 2

    Terima kasih

    • Hai, Kak Heri itu dapat menggunakan SUMIFS
      =SUMIFS(range_jumlah,persentase,">=0.01%",persentase,"<=75%")
      Semoga membantu :)

  14. Tidak Bisa Kak.. Hasilnya #Name?
    Berikut Contohnya kak :
    No. Luas (Ha) %Pencapaian
    1. 20,51 25,6%
    2. 30,11 0%
    3. 16,70 109,7%
    4. 12,39 75,2%
    5. 25,88 38,1%
    6. 19,18 89,9%
    7. 21,37 92,0%
    8. 24,87 15,1%
    9. 28,18 71,2%

    Mohon dibantu kak untuk mencari luas (Ha) dengan rumus SUMIFS sesuai kriteria berikut :
    1. 0% Tidak ada Produksi
    2. 0,01% s/d 75,00% Produksi Rendah
    3. 75,01% sd 90,00% Produksi Sedang
    4. Diatas 90,01% Produksi Tinggi.

    Terima kasih.

    • Hai kak Heri, sepertinya data yang kakak tulis adalah string jika menggunakan bahasa inggris di komputer.
      20,51 sebaiknya ditulis 20.51, begitu juga untuk persen.
      Setelah itu gunakan Rumus
      Kasus No 2 = SUMIFS(B2:B10,C2:C10,">=0.01%",C2:C10,"<=75%")
      dengan B2:B10 range luas dan C2:C10 range pencapaian
      Semoga membantu :)

    • Hai kak Yoppi, kakak bisa melakukan copy paste tabel soal ke Microsoft Word lalu mencoba secara manual formula seperti artikel ini
      Nanti akan saya upload ke server contoh-contohnya
      Semoga membantu 🙂

  16. Selamat sore Min, saya sudah coba berkali-kali tetapi kenapa hasilnya selalu 0 ya untuk sumif lengkap ataupun sumifs?

    • Hai Kak Nur S, coba dipastikan logika sumifs yang digunakan dan tipe data pastikan numerik.
      Kelasalah umum tipe data biasa trjadi saat penggunaan koma sebagai pemisah desimal, misal 2,3 harusnya 2.3
      Semoga membantu 🙂

    • Saya sedang mengikuti contoh yang admin berikan di page ini. Bahkan soalnya pun sama, hasil untuk sumif sederhana berhasil, hanya sumif lengkap dan sumifs

    • Saya mencoba mencari solusi dari permasalahan Kakak Nur S
      Rumus SUMIFS tidak bekerja di Microsoft Office 2010,
      https://answers.microsoft.com/en-us/msoffice/forum/all/sumifs-not-working-in-excel-2010/f6e27b19-ffe2-45a3-8c5a-c6cfb0e9b155
      Sebelumnya kakak pakai versi Ms Office apa?

  18. pagi min... mau tanya kalo tabel di atas kan itu kondisinya "dan" kalo misal saya mau kondisi nya "atau" gimana ya? misal saya mau sum penjualan dengan kriteria MASA KERJA = 1 tahun atau PROVINSI = jawa timur. thx sebelumnya min

    • Hai, Kak Wendy
      Karena SUMIFS menggunakan eksekusi logika berbasis ekspresi kriteria, penggunaan OR dilakukan dengan penjumlahan dalam formula matematika
      OR = OR1 + OR2 - (OR1, OR2)
      Sehingga dapat dibuat formula berikut di Excel
      =SUMIF(range_masa_kerja,"=1",range_penjualan) + SUMIF(range_provinsi,"Jawa Timur",range_penjualan) - SUMIFS(range_penjualan,range_masa_kerja,"=1",range_provinsi,"Jawa Timur")
      Semoga Membantu 🙂

  20. saya mempunyai 2 kolom A KODE 9999 & KOLOM B ( 888,887,886,885) saya validasi memunculkan 9999 kolom zonk contoh rumus =IFERROR(SUMIFS('1.MASTER DATA IN'!$AR:$AR;'1.MASTER DATA IN'!$A:$A;$B$13;'1.MASTER DATA IN'!$D:$D;F$14);"")

1 2

Ayo Berdiskusi Bersama! Belajar Lebih Indah dengan Saling Membantu :)

Tulis komentar
Masukkan nama Anda