XL-mania adalah komunitas yahoogroups Microsoft Excel terbesar di dunia. Melalui XL-mania kita dapat belajar Excel bersama. Fungsi yang dipelajari dari fungsi keuangan, tanggal dan waktu, matematika, statistik, kondisi, lookup, referensi, database, logika, teks, informasi, serta cara membuat user defined function. Selain itu, di XL-mania juga dibahas mengenai penggabungan beberapa fungsi Excel menjadi sebuah formula yang unik, array, dan megaformula. Fitur-fitur Excel yang dibahas juga luas, dari conditional formatting, goal seek, chart, pivot table, macro / VBA, business model, financial modeling, solver, dan lain-lain. Dapatkan tips dan trik excel gratis dari XL-mania! Ingat, malu bertanya, kerja manual!

June 28, 2008

menjumlah berdasar banyak kriteria tanpa SUMIFS

Pertanyaan:
"Halo Excel expert! Saya punya masalah nih. Saya ingin menjumlahkan berdasarkan banyak kriteria. Kasusnya seperti terlampir. Ceritanya saya ingin menjumlahkan berdasarkan syarat1 dan syarat2. Saya ingin yang syarat1-nya "bbb" dan syarat2-nya "222". Jadi yang akan dijumlahkan adalah D4, D6, dan D9. Kalau datanya sedikit sih gampang. Tapi ini datanya banyak buaaannnggeettt. Kata temen saya kalau pakai Excel 2007, tinggal tulis di G6: =SUMIFS(D3:D9,B3:B9,G3,C3:C9,G4). Tapi saya nggak punya Excel 2007. Ada ga cara lain biar ga usah pakai SUMIFS?" - Sandra Dewiana, Jakarta.




Jawab:
Hai Sandra, sepertinya ini nyambung dengan postingan saya tentang fungsi baru di Excel 2007. Sebenarnya ada kok cara lain untuk menjumlah berdasarkan beberapa kriteria / banyak kriteria. Misalnya pada posisi data dan syarat seperti kasus Sandra, di G6 saya tinggal tulis =SUMPRODUCT((B3:B9=G3)*(C3:C9=G4)*D3:D9).


Penjelasan:
  1. =SUMPRODUCT(... berfungsi untuk mencari hasil kali dua buah array.
    ...(B3:B9=G3)... memberikan hasil TRUE jika hasilnya sama dengan G3, FALSE jika tidak sama.
  2. ...(C3:C9=G4)... memberikan hasil TRUE jika hasilnya sama dengan G4, FALSE jika tidak sama.
  3. ...D3:D9... adalah nilai yang akan dijumlahkan



File:
Anda dapat mendownload materi ini di sini dan mendapatkan simulasi yang lebih jelas dalam detail "penjelasan".
[harus memiliki yahoo id dan menjadi member milis XL-mania, tempat belajar excel gratis dengan diskusi yang hebboooohhhh...]

60 comments:

  1. Thanx sharingnya... Kok bisa sih bikin rumus aneh-aneh gini? Belajar di mana bang Abim?

    ReplyDelete
  2. tolong dong cara menjumlahkan
    300000,00
    5000000,00
    500000,00
    300000,00
    600000,00
    100000,00
    190000,00
    3000000,00
    300000,00
    190000,00

    ReplyDelete
  3. @ linda dewi
    hai linda... dewi? tentu saja saya belajar di XL-mania

    @ out from hell
    hai out from hell, penjumlahan macam ini tinggal pakai fungsi =SUM(blok area yang akan dijumlah)

    ReplyDelete
  4. Mas Abim mau tanya boleh dong?
    Gimana caranya menghitung keyword tertentu dan mengurutnya menjadi sepuluh besar. Misalnya suatu klinik dikunjungi 1000 pasien perbulan dengan bermacam diagnosa. gimana caranya membuat laporan 10 besar diagnosa tersebut dan bagaimana cara membuat laporan tersebut ditampilkan perbulan. Terima kasih

    ReplyDelete
  5. salam,
    bagaimana cara menjumlah setiap 100 baris...thx

    ReplyDelete
  6. @ hendi
    halo hendi, misal data diagnosa ada di C2:C2001, tambahkan kolom baru (kolom d) beri judul di d1 "count_diag"; tulis formula di D2
    =kontif($c$2:$c$2001,c2); dobel-klik 'fill-handel' milik d2
    (=otomatis mengkopi d2 ke d2:d2001);
    berlakukan Auto-Filter pada tabel tsb; trus filterlah kolom count_diag dengan kriteria = Top-10

    ReplyDelete
  7. @ anonymouse
    (bagaimana cara menjumlah setiap 100 baris...thx)

    =tergantung bagaimana anda akan meletakkan hasil penjumlahannya.

    misal data yg akan dijumlah ada di A2:A10001
    Jumlah per 100 baris ingin ditulis di B2, B3, B4 dst.
    tulis di B2 formula sbb
    =SUM(OFFSET($A$1,ROW(A1)*100-99,0,100,1))
    copy ke cells di bawahnya sampai menghasilkan 0

    ReplyDelete
  8. minta tolong dong....
    bagaimana menggunakan "fungsi SUMPRODUCT" dengan 3 kriteria dan salah satu kriteria tersebut menggunakan format tanggal (misalnya : mmmm-yyyy). thanks

    contoh :
    nama |bulan |buku |biaya
    adi |march-09 |b.indo |???


    nama |bulan |buku |biaya
    adi |march-09 |b.indo |Rp. 5
    adi |may-09 |b.indo |Rp. 6
    adi |march-09 |b.indo |RP. 5
    adi |june-09 |b.indo |Rp. 8

    ReplyDelete
  9. Minta tlg ya...
    Saya mau jumlahin berdasarkan nama, misal
    /nama/no kwitansi/jumlah/
    /adi/56285/500.000/
    /mita/52886/200.000/
    /adi/58952/50000/
    /mita/95f66/525.000/
    /riko/5f5f2r/250.555/

    Rumus nya gmn ya,,klo mau jumlahin "jumlah" berdasarkan nama nya adi.. Ditunggu ya jawabannya...thx

    ReplyDelete
    Replies
    1. nama no kwitansi jumlah
      adi 56285 500.000
      mita 52886 200.000
      adi 58952 50.000
      mita 95f66 525.000
      riko 5f5f2r 250.555

      adi 550000
      =SUMIF($a$2:$c$6;a8;$c$2:$c$6)

      Delete
  10. bos cara jumlah data html/php yang dicopy ke excel gimana gan

    ReplyDelete
  11. Salam kenal mas Abimo,

    Mohon bantuannya,
    bagaimana membaca tanggal maksimal (data dikolom B)sesuai kolom A/no ID acak dengan kriteria C/no urut ID..misalnya no ID 4

    datanya....
    A/No ID acak B/Tanggal C/No ID Urut D/Hasil (tanggal Maksimal)
    2 11-Feb-11 2
    3 12-Feb-11 3
    4 13-Feb-11 4
    4 19-Feb-11 5
    4 15-Feb-11 6
    5 16-Feb-11 7
    4 17-Feb-11 8
    9 18-Feb-11 9
    8 19-Feb-11 10

    Salam,
    insyirahman

    ReplyDelete
  12. @insyirahman (February 24, 2011 6:09 PM)
    tanya kok pakai Anonym
    menjawabnya kemana dwong ??

    gunakan array formula
    =MAX(($A$2:$A$10=C2)*$B$2:$B$10)

    lebih jelasnya da di sini
    http://www.box.net/shared/trsurfkf2s

    ReplyDelete
  13. minta bantuannya dong...

    bagaimana mencari jumlah pada batas tertentu.
    misal:
    data januari 2011 = 1
    data februari 2011 = 3
    data maret 2011 = 4
    data april 2011 = 5
    data mei 2011 = 3
    data juni 2011 = 2
    data juli 2011 = 3

    bagaimana mencari jumlah data dari bulan awal(januari misalnya) sampai akhir (maret misalnya) sehingga muncul jumlahnya = 8 (1+3+4)??
    atau
    bagaimana mencari jumlah data dari bulan awal(maret misalnya) sampai akhir (juni misalnya) sehingga muncul jumlahnya = 14 (4+5+3+2)??

    terimakasih....

    ReplyDelete
    Replies
    1. Salam kenal buat semuanya aja deh, aq juga mo numpang nnya nih, pertanyaanya sederhana ky punya mas Riefki entuuuu, gmna ya caranya

      Delete
  14. Mau Nanya nih! Bagaimana rumus menghitung jumlah nilai siswa !! tetapi bukan mencari jumlah berapa siswa yg mendapat nilai (rentang nilai) antara misal: 91-95 !!!
    pertanyaan ! misal jumlah siswa yang mendapat nilai antara 91-95 adalah sebanyak 5 siswa, nah berapa jumlah nilai dari 5 siswa yng mendapat nilai antara 91-95 ? Mohon pemecahan masalahnya ya mas!!!

    ReplyDelete
  15. Hai Mas Blogger (yg menulis pesan tgl 21 Juli 2011 11:11 PM)...
    Misalnya Daftar Nilai ada di C6:C31
    Maka rumusnya:
    =SUMPRODUCT((C6:C31>=91)*(C6:C31<=95)*C6:C31)

    Jika pakai Excel 2007, sampeyan punya pilihan fungsi baru: SUMIFS

    ReplyDelete
    Replies
    1. halloo mba cantik.
      salam kenal
      mba,,, saya mau donkz dibantu sama mba, hehe
      saya ada case, dimana saya akan menjumlahkan 2 kriteria menggunakan >= / <=.

      tks
      salam
      niko

      Delete
  16. bagi tips cara menjumlahkan warna dong...mau beli bukunya juga susah...

    ReplyDelete
  17. hai endiwh1968 (posting Sept 15, 2011 10:09 PM)

    Cara paling mudah dengan membaut Fungsi Sendiri,
    lihat contohnya di OnLine_Shared_Folder_"ku":
    http://www.box.net/shared/50ytb9339mh606fydets

    ReplyDelete
  18. @ rekan endwh1968 (post 15 sept 2011)

    ajukan ke milis saja bro, & sertakan contoh datanya nyang mau di itung-itung, dan penjelasan /contoh hasail yg diinginkan; biar nantinya mendapat penjelasan yg oke.

    karena setahuku warna gak bisa dijumlah
    yang bisa dijumlah : bilangan-bilangan yg ada di cell dengan warna tertentu, pada range tertentu.

    yg bisa dihitung/ dicari cacahnya: cells dengan warna yg sama pada range tertentu.

    ReplyDelete
  19. bagus juga nih artikel nya..ijin copas gan, thanks

    ReplyDelete
  20. Gimana caranya menjumlahkan item yang sama dalah sheet yang berbeda.
    Sheet 1
    No Nama Item Jml
    1 Isi Cutter ...
    2 Isolasi ...
    3 Sarung Tangan ...
    4 Solar ...

    Sheet 2.
    No Nama Bagian Nama Item Jumlah
    1 Untung Gudang Isolasi 3
    2 Samuji Extru Isi Cutter 10
    3 Heni PP Cup Isolasi 1
    4 Anton Gudang Isolasi 2
    5 Taslim Tehnik Solar 50
    6 Slamet Packing Sarung Tangan 4

    ReplyDelete
    Replies
    1. Pada sheet aktif, Pencet tombol F1
      Cari Help untuk "CONSOLIDATE"

      Delete
  21. bagaimana cara menggabungkan 2 fungsi yaitu fungsi IF dan MAX tq

    ReplyDelete
    Replies
    1. =MAX(IF(A1:A100>0,A1:A100)
      di akhiri dengan menekan 3 tombol ajaib (Ctrl + Shift + Enter) / ditulis sebagai Array Formula !!

      ArrayFormula ini akan mencari data terkecil
      di A1:A100 tapi yg nilainya di atas 0 (eNol)

      =IF(A1="InginData Terbesar di kolom B", MAX(B:B),IF(A1="InginData Terkecil di kolom B", MIN(B:B)))

      Delete
  22. Sorrry, aku mau tanya dong

    kalau seperti ini
    A B C D E F G H I
    KETERANGAN NAMA JML NAMA JML NAMA JML NAMA
    ANI TINA
    1 PENSIL ANI 5 ANI 4 SANTI 2
    2 BUKU TINA 2 LINA 1 ANI 2

    Nah aku mau cari di kolom H jumlahnya ANI dan kolom I jumlahnya Tina, bagaimana ya ?

    Terima kasih banyak
    5

    ReplyDelete
  23. aku baru nanya sekali ini, mohon dibalas ya...
    misal rentang nilai siswa adalah 40-100. bagaimana formula agar nilai 40-100 dapat dikalikan dari 15% sampai 2% secara halus. sehingga nilai yang rendah tidak dapat menyalip nilai diatasnya berdasarkan pengalinya?

    makasih banyak

    ReplyDelete
  24. di buku mas abimono ga ada menjumlah
    02/02/2012 =120
    12/02/2012 =130
    22/03/2012 =200
    06/04/2012 =100
    10/04/2012 =110
    dst

    hasilnya ingin
    feb =250
    mar =200
    apr =210

    gimana rumusnya ya? mks sebelumnya

    ReplyDelete
  25. hai nanay ayi, posting April 26, 2012

    array formula:
    =SUM(IF(LOWER(TEXT($A$1:$A$5,"MMM"))=LOWER(A9),$B$1:$B$5))

    ReplyDelete
  26. salam kenal semua
    saya ahmad
    bagaimana bila data berikut
    01 153
    01 152
    01 152
    01 15
    01 162
    02 152
    02 16
    02 165
    hasil yang diinginkan
    01 15 = 4
    02 16 = 2
    maksudnya mengcount dengan 2 digit pertama
    mohon petunjuknya para master sekalian.

    ReplyDelete
  27. tambah lagi
    bila data
    01 162 200
    01 171 250
    01 16 350
    01 165 150
    02 17 200
    02 166 400
    02 172 150
    hasil yang diinginkan hampir sama dengan sebelumnya hanya penjumlahannya
    01 16 = 700
    01 17 = 250
    02 17 = 350
    mohon petunjuknya

    ReplyDelete
  28. mau tanya nih... permasalahannya : jika tnggal A = tanggal B dan kode A = kode B maka jumlahkan seluruh transaksi pada tanggal A dengan kode A.
    tanggal 1juni =1juni dan kode AA01=AA01 maka yang ingin di hasilkan ketika kita mengetikan tanggal dan kode pada kolom kode dan tanggal tersebut akan menghasilkan seluruh jumlah yanng berkode A001 pada tanggal 1juni.
    mohon penulisan rumusnya terima kasih

    ReplyDelete
  29. Replies
    1. Rumusnya agak rumit bro, aku lagi bikin tapi masih belum tuntas masih ada masalah sedikit lagi, kalau berminat nanti aku kirim ke email kamu

      Delete
  30. Aku member baru disnini, mo nanya sbb:
    Aku bikin program buku besar pakai excel 2007, dari voucher pengeluaran/penerimaan kas atau bank dan general voucher, otomatis terposting ke buku besarnya masing2; Yang jadi masalah saking banyaknya formula, proses penghitungannya jadi belet padahal sudah pakai 4 processor; Bagaimana cara membuat excel 2007 ini hanya menghitung yang diperlukan saja? Adakah caranya? Thanks ya

    ReplyDelete
  31. Mau tanya nih klo rumus penjumlahan seperti gmn:

    IN-004 500 P-001
    IN-005 200 P-001
    IN-004 400 P-002
    IN-004 100 P-001

    Saya mau hasil penjumlahan dari IN-004 yang P-001
    gmn tuh penulisan rumus nya..maaf lagi belajar..

    terimakasih

    ReplyDelete
  32. AssaLamu'alaikum....
    saya mw tnya,gmn caranya menjumlahkan hasil dalam sheet yg sama tnpa merubah jumlah keseLuruhannya.
    misaLnya;

    TotaL keseLuruhannya 1.000.000
    dari totaL 1.000.000 itu hrz dijabarkan pembelian barang umpamanya senilai 100.000
    nah,jika dr 100.000 itu dijabarkan jumlah pembelian barang seharga 100.000,akan merubah nilai 1.000.000 itu.
    jdi pertnyaan saya gmn caranya agar pembelian barang serta penjabarannya 100.000 itu gak ngaruh ke totaL seLuruhnya..
    mohon penjeLasannya.
    terimakasih...
    :)

    ReplyDelete
  33. Bantuin gan. ini kasusnya
    tanggal
    12-11-12
    10-11-12
    tbd

    12-12-12
    19-12-12

    dari kasus diatas, saya menggunakan sumproduct untuk menggolongkan tanggalnya perbulan seperti: bulan 11 ada 2 dan bulan 12 ada 2 juga. masalahnya terdapat kolom kosong dan karakter berbeda.. gimana nih runmusnya..??
    mohon bantuanya

    ReplyDelete
  34. Halo XL Mania..
    Mau tanya ni, bagaimana cara menghitung warna COnditional Formatting pada suatu range, dan setiap sel pada range punya kriteria CF yang berbeda2? makasi atas bantuannya...!

    ReplyDelete
    Replies
    1. klo pada xl 2007 sudah di lengkapi dengan filter warna, dan penjumlahan bisa dengan menggunakan subtotal,
      =subtotal(9;ref1:ref2:...)

      semoga membantu

      Delete
  35. bisa ngk ya kalau kita pake =sumifs tapi kriterianya ngelink ke filter...
    mohon bantuanya...

    ReplyDelete
  36. Om mau nanya utk sumproduct bisakah dipake pada sheet yang berbeda??? kasih contoh ya:))
    haturnuhun

    ReplyDelete
  37. mas saya mau nanya, kalau menjumlahkan angka yang sederet pada satu baris yang sama pada kolom yang berbeda gmn y? misalnya
    I II
    1 2 3 4 5 6

    I=1+2+3
    II=4+5+6
    bila saya drag kondsinya
    I=1+2+3
    II=2+3+4
    kemudian bila saya merge&centre sebanyak kolom yang disebutkan, sy khawatir bla ada perubahan data pasti susah untuk mengubahnya lagi, datanya buanyak banget mas. dan pastinya saya perlu membuat lebih banyak sheet. mohon bantuannya mas

    ReplyDelete
  38. misi admin, kalo cara menjumlahkan nilai berdasarkan warna gimana ya ?

    TGL IN MASUK TGL OUT KELUAR
    5-Jun 10 30.24 302.40 24-Jun 14 31.56
    6-Jun 6 30.24 181.44 1 32.56
    1 30.24 30.24 1 13.23
    7-Jun 16 31.56 504.96 16 30.24
    2 31.56 63.12 2 31.56
    1 32.56 32.56

    untuk baris pada tanggal 5-jun dan 7-jun, saya warnai dengan warna merah, untuk menghitung jumlah angka di cell yang berwarna merah saja gimana ?

    makasih

    ReplyDelete
    Replies
    1. pada xl 2007 ke atas sih sudah di lengkapi dengan filter warna, sementara untuk penjumlahannya bisa menggunakan subtotal.
      =subtotal(9;ref1:ref2:...)

      semoga membantu

      Delete
  39. Dear XL manias,

    Mohon bantuan, bagaimana caranya/formulanya untuk mendapatkan serial no., part nomor, S/N,P/N dari +/-65.000 data/baris , karena serial no., part nomor, S/N,P/N ada di tengah tengah kalimat dan posisinya tidak tentu (kadang di depan, di tengah atau di akhir kalimat), terima kasih, salam rintoz.

    ReplyDelete
  40. tolong bantu dong.....
    mis;
    kolom ;
    1.4
    2.7
    3.2
    4.4
    5.dst....
    hasilnya kan angka 4 yang paling sering keluar.
    pake Formula =MODE(Range)
    Formula =COUNTIF(Range,kriteria)


    misalnya :
    kolom
    1.2365
    2.6345
    3.2245
    4.dst....

    bagaimana rumus nya untuk mencari angka yang paling sering keluar...kan rumusnya bisanya cuma 1 angka doang. tapi kalo ribuan kaya gitu ko ga bisa ya..tolong bantu.tks.

    ReplyDelete
  41. Hai all, mau tanya dong

    Gimana caranya kalo mau remove duplikat tanpa menggunakan tools Remove Duplicates.

    Terima kasih

    Romel

    ReplyDelete
  42. Hii all xl mania,

    mau tanya dunk? cara membuat formula untuk mecari/memeilih tanggal seagai jumlah yang akan ditampilkan dalam sebuah laporan sebagai berikut.
    data sumber
    nama tanggal ujian nilai
    a 16-Dec-2013 baik
    b 17-Dec-2013 baik
    c 1-Dec-2013 buruk
    d 3-Dec-2013 kurang baik
    e 2-Dec-2013 baik
    f 20-Dec-2013 baik

    Week 01 Week 02 week 03 week 04
    baik ?
    kurang baik ?
    buruk ?

    bagaimana membuat formula untuk mencari di week01 dst bulan desember sesuai dengan data keadaan nilainya,

    terima kasih

    ReplyDelete
  43. Terima kasih buwanyak gan, aku praktekkan langsung bisa JOS penjelasannya singkat padat berisi bangetz ;)

    ReplyDelete
  44. =SUMPRODUCT(--(LEFT('Pekerjaan Tahun 2012'!D4:D417,1)=B4))&(COUNTIFS('Pekerjaan Tahun 2012'!H4:H417,"rutin",'Pekerjaan Tahun 2012'!R4:R417,"received"))


    index apa yg cocok untuk mendapat hasil dari formula aray 1 (sumproduct left)

    ReplyDelete
  45. matur thank you xl mania........

    ReplyDelete
  46. mohon bantuannya saya ada kasus seperti ini :
    Kolom A Kolom B Kolom C Kolom D Kolom E
    a 200 a 15 ?
    b 300 a 30
    c 120 c 40
    untuk mendapatkan Jumlah Kolom B - Kolom D sesuai dengan Jumlah di Kolom C, bagaiamana ya

    ReplyDelete
  47. Numpang tanya Admin ,
    Kalau boleh mau tanya , misal saya punya data data acak sbb :

    Nilai Warna
    10 Biru
    7 Kuning
    8 Biru
    -5 Merah
    7 Kuning
    8 Merah

    Kemudian ingin dijumlahkan data2 di atas sambil disederhanakan sehingga hasilnya sbb :
    18 Biru
    14 Kuning
    3 Merah

    Rumusnya bagaimana ya ? Terima kasih sebelumnya

    ReplyDelete
  48. Mohon bantuannya????
    saya punya data, baris 1 (kolom A-E): Merah, Hijau, Kuning, Hitam, Biru. Baris ke 2 (kolom A-E): +, -, +, ,+. Baris ke 3 (kolom A-E): -, +, , ,+.
    gimana caranya agar hasilnya untuk baris kedua dapat digabung berdasarkan kriteria baris 1, misal pada cel A4 : merah + Hijau - kuning + Biru +. (ket. hitam tidak muncul karena tidak ada datanya) . trimakasih.......

    ReplyDelete
  49. MOHON BANTUANNYA
    SAYA ADA DATA

    SS S SS J TP KK SS S TP TP KK J J SS S KK J TP

    MISAL SAYA MAU JUMLAHKAN UNTUK SS SAJA, BAGAIMANA CARANYA?
    TERIMAKASIH

    ReplyDelete
  50. Mohon bantuannya admin,

    Misalkan database transaksi pelanggan yang memiliki ratusan nama. dengan 1 nama bisa beberapa kali transaksi sehingga menjadi ribuan data. Jika saya ingin membuat list / mengetahui total transaksi per nama customer bagaimanakah rumusnya?

    Terima Kasih

    ReplyDelete
  51. Dear Admin, mohon bantuannya:
    saya punya data sbb:

    1101 (nama Kamar Tamu)
    Toilet AC Balcony Door Parquet Floor
    22 nov 14 15 oct 14 10 sep 14

    1102
    Toilet AC Balcony Door Parquet Floor
    12 aug 14 17 oct 14 5 nov 14 1 july 14

    INi data pemeliharaan di kamar kamar tamu
    jumlah kamar ada 1000 kamar

    Bagaimana menghitung / mengelompokkan agar hasilnya spt ini

    Toilet AC Balcony door Parquet Floor
    Okt 5 oct 0 oct 9 oct 4
    nov 6 nov 1 nov 4 nov10

    jadi, bisa diketahui, berapa yang sudah diselesaikan stiap bulan utk masing masing kriteria

    terima kasih
    email: santimodesta@gmail.com

    ReplyDelete
  52. MOHON INFONYA DONG.

    saya mau menjumlahkan data menggunakan kata tengah

    Contoh : di kolom A44 = total PPN Rp.

    dan disalah satu kolom f4:f10 hanya kata PPN
    sedangkan nilainya di kolom g4:g10

    farid07022013@gmail.com

    ReplyDelete

Silahkan berkomentar tentang XL-mania...