{"id":3158,"date":"2023-07-18T22:40:17","date_gmt":"2023-07-18T22:40:17","guid":{"rendered":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/"},"modified":"2023-07-18T22:40:17","modified_gmt":"2023-07-18T22:40:17","slug":"sumif-subtotal-excel","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/","title":{"rendered":"Cara menggunakan subtotal dengan sumif di excel"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\">Anda bisa menggunakan rumus berikut untuk menggabungkan fungsi <strong>SUBTOTAL<\/strong> dan <b>SUMIF<\/b> di Excel:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <strong>=SUMPRODUCT(SUBTOTAL(109,OFFSET( <span style=\"color: #3366ff;\">C2<\/span> ,ROW( <span style=\"color: #ff0000;\">C2:C11<\/span> )-ROW( <span style=\"color: #3366ff;\">C2<\/span> ),,1)),--( <span style=\"color: #800080;\">B2:B11<\/span> =\"Guard\"))\n<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Rumus khusus ini memungkinkan Anda menjumlahkan nilai dalam rentang C2:C11 di mana nilai terkait dalam rentang B2:B11 sama dengan &#8220;Simpan&#8221; bahkan setelah rentang sel tersebut difilter dengan cara tertentu.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.<\/span><\/p>\n<h3> <span style=\"color: #000000;\"><strong>Contoh: Cara menggunakan SUBTOTAL dengan SUMIF di Excel<\/strong><\/span><\/h3>\n<p> <span style=\"color: #000000;\">Misalkan kita mempunyai kumpulan data berikut yang berisi informasi tentang berbagai pemain bola basket:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26824 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif1.jpg\" alt=\"\" width=\"470\" height=\"434\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Selanjutnya, mari kita filter data untuk hanya menampilkan baris tempat pemain Wilayah <strong>Barat<\/strong> berada.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Untuk melakukan ini, sorot rentang sel <strong>A1:C11<\/strong> . Kemudian klik pada tab <b>Data<\/b> di sepanjang pita atas dan klik tombol <strong>Filter<\/strong> .<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26826 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif2.jpg\" alt=\"\" width=\"668\" height=\"428\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Kemudian klik panah drop-down di sebelah <b>Conference<\/b> dan pastikan hanya kotak di sebelah <strong>West<\/strong> yang dicentang, lalu klik <strong>OK<\/strong> :<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26827 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif3.jpg\" alt=\"\" width=\"494\" height=\"568\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Data akan secara otomatis disaring untuk menampilkan hanya baris dimana kolom Conference sama dengan <strong>West<\/strong> :<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26828 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif4.jpg\" alt=\"\" width=\"454\" height=\"403\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Jika kita mencoba menggunakan fungsi <strong>SUMIF()<\/strong> untuk menjumlahkan nilai pada kolom Poin dimana nilai kolom Posisi sama dengan &#8220;Penjaga&#8221;, maka sebenarnya akan mengembalikan jumlah poin untuk baris yang sama dengan &#8220;Penjaga&#8221; pada SUMIF() fungsi. kumpulan data asli:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26837 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/sous-totalsumif1.jpg\" alt=\"\" width=\"503\" height=\"380\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Sebagai gantinya, kita perlu menggunakan rumus berikut:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <strong>=SUMPRODUCT(SUBTOTAL(109,OFFSET( <span style=\"color: #3366ff;\">C2<\/span> ,ROW( <span style=\"color: #ff0000;\">C2:C11<\/span> )-ROW( <span style=\"color: #3366ff;\">C2<\/span> ),,1)),--( <span style=\"color: #800080;\">B2:B11<\/span> =\"Guard\"))<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Tangkapan layar berikut menunjukkan cara menggunakan rumus ini dalam praktik:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26838 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/sous-totalsumif2.jpg\" alt=\"\" width=\"453\" height=\"379\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Rumus ini mengembalikan jumlah <strong>85<\/strong> yang benar.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Kami dapat mengonfirmasi kebenarannya dengan menghitung secara manual jumlah nilai poin untuk baris yang kolom Posisinya sama dengan &#8220;Penjaga&#8221;:<\/span><\/p>\n<p> <span style=\"color: #000000;\">Jumlah poin penjagaan: 12 + 28 + 30 + 15 = <strong>85<\/strong> .<\/span><\/p>\n<h3> <strong><span style=\"color: #000000;\">Sumber daya tambahan<\/span><\/strong><\/h3>\n<p> <span style=\"color: #000000;\">Tutorial berikut menjelaskan cara melakukan operasi umum lainnya di Excel:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/id\/excel-menghapus-baris-yang-difilter\/\" target=\"_blank\" rel=\"noopener\">Cara menghapus baris yang difilter di Excel<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/excel-menghitung-baris-yang-difilter\/\" target=\"_blank\" rel=\"noopener\">Cara menghitung baris yang difilter di Excel<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/baris-filter-jumlah-excel\/\" target=\"_blank\" rel=\"noopener\">Bagaimana cara menambahkan baris yang difilter di Excel<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Anda bisa menggunakan rumus berikut untuk menggabungkan fungsi SUBTOTAL dan SUMIF di Excel: =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),&#8211;( B2:B11 =&#8221;Guard&#8221;)) Rumus khusus ini memungkinkan Anda menjumlahkan nilai dalam rentang C2:C11 di mana nilai terkait dalam rentang B2:B11 sama dengan &#8220;Simpan&#8221; bahkan setelah rentang sel tersebut difilter dengan cara tertentu. Contoh berikut menunjukkan cara [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-18T22:40:17+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif1.jpg\" \/>\n<meta name=\"author\" content=\"Benjamin anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Ditulis oleh\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin anderson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/\",\"url\":\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/\",\"name\":\"Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-18T22:40:17+00:00\",\"dateModified\":\"2023-07-18T22:40:17+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cara menggunakan subtotal dengan sumif di excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/statorials.org\/id\/#website\",\"url\":\"https:\/\/statorials.org\/id\/\",\"name\":\"Statorials\",\"description\":\"Panduan anda untuk kompetensi statistik!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/statorials.org\/id\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"id\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\",\"name\":\"Benjamin anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"contentUrl\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"caption\":\"Benjamin anderson\"},\"description\":\"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya\",\"sameAs\":[\"http:\/\/statorials.org\/id\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials","description":"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/","og_locale":"id_ID","og_type":"article","og_title":"Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials","og_description":"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.","og_url":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/","og_site_name":"Statorials","article_published_time":"2023-07-18T22:40:17+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif1.jpg"}],"author":"Benjamin anderson","twitter_card":"summary_large_image","twitter_misc":{"Ditulis oleh":"Benjamin anderson","Estimasi waktu membaca":"1 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/","url":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/","name":"Cara Menggunakan SUBTOTAL dengan SUMIF di Excel \u2013 Statorials","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-18T22:40:17+00:00","dateModified":"2023-07-18T22:40:17+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara menggunakan fungsi SUBTOTAL dengan fungsi SUMIF di Excel, beserta contohnya.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/sumif-subtotal-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/sumif-subtotal-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Cara menggunakan subtotal dengan sumif di excel"}]},{"@type":"WebSite","@id":"https:\/\/statorials.org\/id\/#website","url":"https:\/\/statorials.org\/id\/","name":"Statorials","description":"Panduan anda untuk kompetensi statistik!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/statorials.org\/id\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"id"},{"@type":"Person","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81","name":"Benjamin anderson","image":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/","url":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","contentUrl":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","caption":"Benjamin anderson"},"description":"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya","sameAs":["http:\/\/statorials.org\/id"]}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/3158"}],"collection":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/comments?post=3158"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/3158\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=3158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=3158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=3158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}