Bab 7: Conditional Aggregation (SUMIFS, COUNTIFS, AVERAGEIFS)

Kenapa Perlu Aggregation Bersyarat

Bayangkan kamu memiliki data transaksi Belanjain selama satu bulan penuh, dengan ribuan baris data yang mencakup berbagai kategori produk, kota pengiriman, dan metode pembayaran. Jika kamu hanya ingin mengetahui total keseluruhan penjualan bulan tersebut, fungsi SUM sederhana sudah cukup, cukup menjumlahkan seluruh nilai pada kolom nilai transaksi. Namun pertanyaan bisnis yang sebenarnya jarang sesederhana itu. Tim manajemen biasanya ingin tahu hal yang lebih spesifik, misalnya berapa total penjualan khusus kategori Fashion, atau berapa total penjualan di kota Jakarta yang menggunakan metode pembayaran transfer bank pada bulan tersebut.

Pertanyaan-pertanyaan semacam ini membutuhkan cara menjumlahkan data yang hanya mengambil baris-baris tertentu yang memenuhi kriteria tertentu, bukan seluruh data. Di sinilah fungsi aggregation bersyarat berperan, yaitu SUMIFS untuk menjumlahkan, COUNTIFS untuk menghitung banyaknya baris, dan AVERAGEIFS untuk menghitung rata-rata, yang ketiganya bisa disaring berdasarkan satu kriteria atau bahkan banyak kriteria sekaligus dalam satu formula yang sama.

SUMIFS: Menjumlahkan dengan Banyak Kriteria

SUMIFS digunakan untuk menjumlahkan nilai pada sebuah kolom, namun hanya untuk baris-baris yang memenuhi satu atau lebih kriteria yang ditentukan. Struktur penulisannya adalah =SUMIFS(range_yang_dijumlahkan, range_kriteria_1, kriteria_1, [range_kriteria_2, kriteria_2, ...]). Perhatikan bahwa argumen pertama selalu berupa range yang berisi angka yang ingin dijumlahkan, baru diikuti pasangan range kriteria dan kriterianya, dan kamu bisa menambahkan pasangan kriteria sebanyak yang dibutuhkan.

Sebagai ilustrasi paling sederhana dengan satu kriteria, bayangkan data transaksi Belanjain memiliki kolom Kategori di kolom B dan kolom Nilai Transaksi di kolom D. Jika kamu ingin menjumlahkan total nilai transaksi khusus kategori "Fashion", formulanya ditulis sebagai =SUMIFS(D:D, B:B, "Fashion"). Formula ini akan menjumlahkan seluruh nilai di kolom D, namun hanya untuk baris-baris di mana kolom B bernilai "Fashion".

Kekuatan sebenarnya dari SUMIFS terlihat ketika kriteria yang dibutuhkan lebih dari satu. Melanjutkan contoh sebelumnya, jika kolom Kota berada di kolom C dan kamu ingin menjumlahkan total nilai transaksi khusus kategori "Fashion" yang dikirim ke kota "Jakarta", formulanya ditulis sebagai =SUMIFS(D:D, B:B, "Fashion", C:C, "Jakarta"). Perhatikan pola penulisannya, setiap kriteria tambahan selalu ditulis sebagai pasangan range kriteria dan nilai kriterianya, dan seluruh kriteria ini bekerja dengan logika AND, artinya baris data yang dihitung hanya baris yang memenuhi seluruh kriteria sekaligus, bukan salah satu saja.

SUMIFS juga mendukung kriteria berupa perbandingan angka, tidak hanya kecocokan teks. Sebagai ilustrasi, jika kamu ingin menjumlahkan total nilai transaksi yang nilainya di atas Rp200 ribu, formulanya ditulis sebagai =SUMIFS(D:D, D:D, ">200000"). Perhatikan bahwa pada kasus ini, range yang dijumlahkan dan range kriteria adalah kolom yang sama, yaitu kolom D itu sendiri, karena kriteria yang diperiksa memang berasal dari nilai transaksi itu sendiri, bukan dari kolom lain.

COUNTIFS: Menghitung Banyaknya Baris dengan Banyak Kriteria

COUNTIFS bekerja dengan logika yang serupa dengan SUMIFS, namun alih-alih menjumlahkan nilai, fungsi ini menghitung berapa banyak baris yang memenuhi kriteria tertentu. Struktur penulisannya adalah =COUNTIFS(range_kriteria_1, kriteria_1, [range_kriteria_2, kriteria_2, ...]). Perhatikan bahwa COUNTIFS tidak memerlukan argumen range yang dijumlahkan seperti pada SUMIFS, karena fungsi ini hanya menghitung banyaknya baris, bukan menjumlahkan nilai tertentu.

Sebagai ilustrasi, jika kamu ingin mengetahui berapa banyak transaksi yang terjadi pada kategori "Elektronik", formulanya ditulis sebagai =COUNTIFS(B:B, "Elektronik"). Jika kamu ingin mempersempit lagi menjadi berapa banyak transaksi kategori "Elektronik" yang menggunakan metode pembayaran "Transfer Bank", dengan asumsi kolom metode pembayaran berada di kolom E, formulanya ditulis sebagai =COUNTIFS(B:B, "Elektronik", E:E, "Transfer Bank").

COUNTIFS sangat berguna untuk kebutuhan seperti menghitung jumlah pelanggan unik yang memenuhi kriteria tertentu, menghitung berapa banyak transaksi yang gagal atau dibatalkan pada periode tertentu, atau menghitung berapa banyak produk dalam kategori tertentu yang stoknya berada di bawah ambang batas minimum. Fungsi ini menjadi dasar penting untuk berbagai perhitungan metrik bisnis yang sudah dibahas di Bab 3, misalnya untuk menghitung jumlah pelanggan baru pada periode tertentu sebagai bagian dari perhitungan CAC atau Conversion.

AVERAGEIFS: Menghitung Rata-Rata dengan Banyak Kriteria

AVERAGEIFS menggabungkan logika SUMIFS dan COUNTIFS untuk menghasilkan rata-rata dari baris-baris yang memenuhi kriteria tertentu. Struktur penulisannya mirip dengan SUMIFS, yaitu =AVERAGEIFS(range_yang_dirata-ratakan, range_kriteria_1, kriteria_1, [range_kriteria_2, kriteria_2, ...]).

Sebagai ilustrasi, jika kamu ingin mengetahui rata-rata nilai transaksi khusus kategori "Kecantikan", formulanya ditulis sebagai =AVERAGEIFS(D:D, B:B, "Kecantikan"). Formula ini secara otomatis menjumlahkan seluruh nilai transaksi kategori Kecantikan, lalu membaginya dengan jumlah transaksi kategori tersebut, menghasilkan rata-rata nilai transaksi tanpa kamu perlu menghitung SUMIFS dan COUNTIFS secara terpisah lalu membaginya secara manual.

Satu hal penting yang perlu diperhatikan ketika menggunakan AVERAGEIFS adalah fungsi ini akan menghasilkan error jika tidak ada satu pun baris yang memenuhi kriteria yang ditentukan, karena secara matematis tidak mungkin menghitung rata-rata dari nol data. Situasi ini berbeda dengan SUMIFS maupun COUNTIFS yang akan menghasilkan angka nol jika tidak ada data yang memenuhi kriteria, bukan error. Oleh karena itu, ketika bekerja dengan AVERAGEIFS, terutama pada kriteria yang mungkin saja tidak memiliki data sama sekali (misalnya kategori produk yang baru saja ditambahkan dan belum ada transaksinya), penting untuk mengantisipasi kemungkinan error ini, misalnya dengan membungkusnya menggunakan fungsi penanganan error seperti yang sudah dibahas pada Bab 6.

Perbedaan dengan SUMIF, COUNTIF, dan AVERAGEIF

Salah satu sumber kebingungan yang cukup umum bagi pemula adalah keberadaan fungsi SUMIF, COUNTIF, dan AVERAGEIF (tanpa huruf S di akhir), yang namanya sangat mirip dengan SUMIFS, COUNTIFS, dan AVERAGEIFS yang sudah dibahas di atas. Perbedaan mendasarnya terletak pada jumlah kriteria yang bisa dipakai. Fungsi tanpa huruf S di akhir (SUMIF, COUNTIF, AVERAGEIF) hanya bisa menerima satu kriteria saja, sementara fungsi dengan huruf S di akhir (SUMIFS, COUNTIFS, AVERAGEIFS) bisa menerima satu kriteria maupun banyak kriteria sekaligus.

Selain perbedaan jumlah kriteria, terdapat perbedaan kecil namun penting dalam urutan penulisan argumen antara SUMIF dan SUMIFS. Pada SUMIF, urutan argumennya adalah range kriteria terlebih dahulu, baru kriteria, baru range yang dijumlahkan, dengan struktur =SUMIF(range_kriteria, kriteria, range_yang_dijumlahkan). Sementara pada SUMIFS, seperti sudah dibahas sebelumnya, range yang dijumlahkan justru ditulis di awal, baru diikuti pasangan range kriteria dan kriterianya. Perbedaan urutan argumen inilah yang sering menjadi sumber kesalahan ketika seseorang terbiasa memakai salah satu fungsi lalu beralih ke fungsi lainnya tanpa memperhatikan perbedaan urutan ini.

Mengingat SUMIFS, COUNTIFS, dan AVERAGEIFS tetap bisa dipakai meskipun hanya dengan satu kriteria saja, dan strukturnya lebih konsisten satu sama lain, sebagai kebiasaan yang baik, disarankan untuk terbiasa langsung memakai versi dengan huruf S di akhir sejak awal, meskipun kebutuhan saat itu hanya satu kriteria. Kebiasaan ini akan menghindari kebingungan berpindah antar struktur formula yang berbeda, dan mempermudah jika di kemudian hari kriteria yang dibutuhkan bertambah menjadi lebih dari satu.

Menggabungkan Ketiga Fungsi dalam Analisis Nyata

Untuk melihat bagaimana ketiga fungsi ini bekerja bersama dalam praktik nyata, mari kembali ke konteks Belanjain. Bayangkan tim manajemen ingin memahami performa kategori Fashion secara lebih mendalam pada bulan tertentu, khususnya untuk transaksi yang dikirim ke kota Surabaya. Dengan menggunakan SUMIFS, kamu bisa menghitung total nilai penjualan kategori Fashion di Surabaya. Dengan COUNTIFS, kamu bisa menghitung berapa banyak transaksi yang terjadi dengan kriteria yang sama. Dan dengan AVERAGEIFS, kamu bisa menghitung rata-rata nilai per transaksi untuk kriteria tersebut, yang jika dibandingkan dengan rata-rata nilai transaksi kategori Fashion secara keseluruhan (tanpa filter kota), bisa memberikan insight apakah pelanggan di Surabaya cenderung berbelanja dalam nilai yang lebih besar atau lebih kecil dibanding rata-rata nasional.

Ketiga angka ini, total, banyaknya transaksi, dan rata-rata, ketika disajikan bersama-sama akan memberikan gambaran yang jauh lebih utuh dibanding hanya menampilkan satu angka saja. Sebagai ilustrasi, dua kota bisa saja memiliki total nilai penjualan yang sama persis, namun kota pertama mencapainya dari banyak transaksi dengan nilai kecil, sementara kota kedua mencapainya dari sedikit transaksi dengan nilai besar. Tanpa melihat ketiga angka ini bersama-sama, perbedaan pola semacam ini akan mudah terlewat, padahal bisa jadi memiliki implikasi bisnis yang cukup berbeda, misalnya strategi pemasaran yang perlu diterapkan pada kedua kota tersebut sebaiknya tidak disamakan begitu saja.