Cara menggunakan subtotal dengan sumproduk di excel

Anda bisa menggunakan rumus berikut untuk menggabungkan fungsi SUBTOTAL dan SUMPRODUK di Excel:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Rumus khusus ini memungkinkan Anda menjumlahkan produk nilai rentang C2:C11 dan rentang D2:D11 bahkan setelah rentang sel tersebut difilter dengan cara tertentu.

Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.

Contoh: Cara menggunakan SUBTOTAL dengan SUMPRODUK di Excel

Misalkan kita memiliki kumpulan data berikut yang berisi informasi tentang penjualan berbagai produk di dua toko kelontong berbeda:

Selanjutnya, mari kita filter datanya agar hanya menampilkan baris yang nilainya di kolom Store adalah B.

Untuk melakukan ini, sorot rentang sel A1:D11 . Kemudian klik pada tab Data di sepanjang pita atas dan klik tombol Filter .

Kemudian klik panah drop-down di sebelah Store dan pastikan hanya kotak di sebelah B yang dicentang, lalu klik OK :

Data akan secara otomatis difilter untuk hanya menampilkan baris yang kolom Store-nya sama dengan B :

Jika kita mencoba menggunakan fungsi SUMRPODUCT() untuk menjumlahkan produk dari nilai kolom Penjualan dan Harga, fungsi tersebut akan mengembalikan jumlah produk dari dua kolom ini dalam kumpulan data asli:

Sebagai gantinya, kita perlu menggunakan rumus berikut:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini dalam praktik:

Rumus ini mengembalikan jumlah 77.5 yang benar.

Kami dapat mengonfirmasi kebenarannya dengan menghitung secara manual jumlah produk dari nilai antara kolom Penjualan dan Harga:

Jumlah hasil kali nilai antara penjualan dan harga: (3*2) + (12*2.5) + (5*3.5) + (8*3) = 77.5 .

Sumber daya tambahan

Tutorial berikut menjelaskan cara melakukan operasi umum lainnya di Excel:

Cara menghapus baris yang difilter di Excel
Cara menghitung baris yang difilter di Excel
Cara menjumlahkan baris yang difilter di Excel