Menggabungkan Rumus SUMIF dan SUBTOTAL Pada Google Sheets

Sudah sering beberapa kali ditanyakan bagaimana cara menggabungkan rumus SUMIF dan SUBTOTAL namun baru sempat saya bahas kali ini.

Rumus SUMIF adalah rumus yang digunakan untuk mejumlahkan nilai berdasarkan kriteria, Sedangkan rumus SUBTOTAL adalah rumus yang didalamnya memiliki beberapa fungsi seperti COUNTA, MAX, MIN, SUM dan lain lain dengan kelebihan dari rumus SUBTOTAL adalah bisa menghitung data yang tampil saja pada data Filter.

Secara teori seharusnya kriteria filter bisa dimasukan kedalam rumus SUMIF jika ingin menghitung data sesuai data ter-Fiter.

Tetapi pada kenyatannya gabungan SUMIF dan SUBTOTAL ini tetap diperlukan agar proses Filter ini bisa dilakukan secara langsung tanpa perlu mengubah ubah rumus SUMIF.

Sebagai contoh misalnya menggunakan rumus SUMIF untuk Agen kota Bandung dan di Filter pada buah Apel

Rumus SUMIF tetap menghasilkan 23.000 dimana data yang tampil seharusnya 19.000 saja. Karena itulah peran SUBTOTAL disini dibutuhkan

Gabung Rumus SUMIF SUBTOTAL dengan BYROW

Untuk menggunakan SUMIF dengan SUBTOTAL, cara pertama adalah dengan menambahkan formula BYROW.

Formula ini akan menghasilkan Nilai secara baris perbaris sesuai Range ARRAY dan diolah dalam rumus LAMBDA

Untuk contohnya bisa dilihat pada gambar dibawah ini

=SUMIFS(D3:D11, BYROW(D3:D11,LAMBDA(A,SUBTOTAL(3,A))),1,B3:B11,F2)

Hasilnya bisa dilihat pada gambar dibawah ini, dimana filter data mempengaruhi hasil SUMIF

Alternatif Rumus Kedua Dengan MAP

Untuk cara kedua ini yaitu menggunakan rumus MAP, rumus MAP hampir sama dengan BYROW bedanya MAP bisa diisi dengan beberapa Range, sedangkan BYROW hanya 1 range saja.

=SUM(MAP(B3:B11,D3:D11,LAMBDA(A,B,IF(AND(SUBTOTAL(3,A),A=F2),B))))