Cara menggunakan subtotal dengan sumif di excel

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Rumus khusus ini memungkinkan Anda menjumlahkan nilai dalam rentang C2:C11 di mana nilai terkait dalam rentang B2:B11 sama dengan “Simpan” bahkan setelah rentang sel tersebut difilter dengan cara tertentu.

Contoh berikut menunjukkan cara menggunakan rumus ini dalam praktik.

Contoh: Cara menggunakan SUBTOTAL dengan SUMIF di Excel

Misalkan kita mempunyai kumpulan data berikut yang berisi informasi tentang berbagai pemain bola basket:

Selanjutnya, mari kita filter data untuk hanya menampilkan baris tempat pemain Wilayah Barat berada.

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

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

Data akan secara otomatis disaring untuk menampilkan hanya baris dimana kolom Conference sama dengan West :

Jika kita mencoba menggunakan fungsi SUMIF() untuk menjumlahkan nilai pada kolom Poin dimana nilai kolom Posisi sama dengan “Penjaga”, maka sebenarnya akan mengembalikan jumlah poin untuk baris yang sama dengan “Penjaga” pada SUMIF() fungsi. kumpulan data asli:

Sebagai gantinya, kita perlu menggunakan rumus berikut:

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

Tangkapan layar berikut menunjukkan cara menggunakan rumus ini dalam praktik:

Rumus ini mengembalikan jumlah 85 yang benar.

Kami dapat mengonfirmasi kebenarannya dengan menghitung secara manual jumlah nilai poin untuk baris yang kolom Posisinya sama dengan “Penjaga”:

Jumlah poin penjagaan: 12 + 28 + 30 + 15 = 85 .

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
Bagaimana cara menambahkan baris yang difilter di Excel