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...]
klik-klik 
coretan abimono pas tanggal 6/28/2008 07:30:00 AM
tanda artikel , , ,

23 komentar iseng:

  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
  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
  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
  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

Silahkan berkomentar tentang XL-mania...

Subscribe to: Post Comments (Atom)