{"id":3157,"date":"2023-07-18T22:44:55","date_gmt":"2023-07-18T22:44:55","guid":{"rendered":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/"},"modified":"2023-07-18T22:44:55","modified_gmt":"2023-07-18T22:44:55","slug":"hitungan-subtotal-excel","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/","title":{"rendered":"Cara menggunakan subtotal dengan countif 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 <strong>COUNTIF<\/strong> di Excel:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <strong>=SUMPRODUCT(SUBTOTAL(3,OFFSET( <span style=\"color: #3366ff;\">B2:B11<\/span> ,ROW( <span style=\"color: #3366ff;\">B2:B11<\/span> )-ROW( <span style=\"color: #ff0000;\">B2<\/span> ),0,1)),--( <span style=\"color: #3366ff;\">B2:B11<\/span> =\"Guard\"))\n<\/strong><\/pre>\n<p> <span style=\"color: #000000;\">Rumus khusus ini memungkinkan Anda menghitung jumlah sel dalam rentang B2:B11 yang sama dengan &#8220;Penjaga&#8221; bahkan setelah rentang sel tersebut telah 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 COUNTIF 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>COUNTIF()<\/strong> untuk menghitung jumlah baris dengan Posisi sama dengan &#8220;Penjaga&#8221;, fungsi tersebut akan mengembalikan jumlah baris yang sama dengan &#8220;Penjaga&#8221; pada kumpulan data asli:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-26829 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif5.jpg\" alt=\"\" width=\"466\" height=\"375\" 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(3,OFFSET( <span style=\"color: #3366ff;\">B2:B11<\/span> ,ROW( <span style=\"color: #3366ff;\">B2:B11<\/span> )-ROW( <span style=\"color: #ff0000;\">B2<\/span> ),0,1)),--( <span style=\"color: #3366ff;\">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-26830 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/nombre-de-sous-totalif6.jpg\" alt=\"\" width=\"523\" height=\"404\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Fungsi ini hanya menghitung jumlah garis yang terlihat dimana Posisinya sama dengan \u201cPenjaga\u201d, yaitu <strong>4<\/strong> garis.<\/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 COUNTIF di Excel: =SUMPRODUCT(SUBTOTAL(3,OFFSET( B2:B11 ,ROW( B2:B11 )-ROW( B2 ),0,1)),&#8211;( B2:B11 =&#8221;Guard&#8221;)) Rumus khusus ini memungkinkan Anda menghitung jumlah sel dalam rentang B2:B11 yang sama dengan &#8220;Penjaga&#8221; bahkan setelah rentang sel tersebut telah difilter dengan cara tertentu. Contoh berikut menunjukkan cara menggunakan rumus ini dalam [&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 COUNTIF di Excel \u2013 Statorials<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF 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\/hitungan-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 COUNTIF di Excel \u2013 Statorials\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF di Excel, beserta contohnya.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-18T22:44:55+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\/hitungan-subtotal-excel\/\",\"url\":\"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/\",\"name\":\"Cara Menggunakan SUBTOTAL dengan COUNTIF di Excel \u2013 Statorials\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-18T22:44:55+00:00\",\"dateModified\":\"2023-07-18T22:44:55+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF di Excel, beserta contohnya.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cara menggunakan subtotal dengan countif 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 COUNTIF di Excel \u2013 Statorials","description":"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF 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\/hitungan-subtotal-excel\/","og_locale":"id_ID","og_type":"article","og_title":"Cara Menggunakan SUBTOTAL dengan COUNTIF di Excel \u2013 Statorials","og_description":"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF di Excel, beserta contohnya.","og_url":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/","og_site_name":"Statorials","article_published_time":"2023-07-18T22:44:55+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\/hitungan-subtotal-excel\/","url":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/","name":"Cara Menggunakan SUBTOTAL dengan COUNTIF di Excel \u2013 Statorials","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-18T22:44:55+00:00","dateModified":"2023-07-18T22:44:55+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara menggabungkan fungsi SUBTOTAL dan COUNTIF di Excel, beserta contohnya.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/hitungan-subtotal-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Cara menggunakan subtotal dengan countif 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\/3157"}],"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=3157"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/3157\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=3157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=3157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=3157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}