Cara Menggunakan COUNTIFS, SUMIFS, AVERAGEIFS di Excel

Tiga rumus yang paling umum digunakan di Excel yang melakukan perhitungan matematika sederhana adalah MENGHITUNG, JUMLAH dan RATA-RATA. Apakah Anda mengelola anggaran keuangan di Excel atau sekadar melacak liburan Anda berikutnya, Anda mungkin pernah menggunakan salah satu dari fungsi ini sebelumnya.

Pada artikel ini, kita akan membahas dasar-dasar ketiga fungsi ini dan rekanan yang relevan dan berguna: COUNTIFS, SUMIFS, dan AVERAGEIFS.

Katakanlah kita sedang memulai bisnis online baru yang menjual ponsel dan kita memiliki lembar yang mencantumkan penjualan yang telah kita buat dalam dua bulan pertama. Unduh contoh spreadsheet Excel di sini.

Excel COUNT, SUM, dan AVERAGE

Untuk mengetahui jumlah ponsel yang telah kami jual, kami dapat dengan cepat menggunakan MENGHITUNG rumus seperti yang ditunjukkan di bawah ini:

= COUNT (E2: E16)

Di sisi lain, untuk mendapatkan jumlah total penjualan yang telah kami buat, kami dapat menggunakan JUMLAH rumus seperti yang ditunjukkan di bawah ini:

= SUM (E2: E16)

Terakhir, untuk mengetahui rata-rata penjualan yang kami buat untuk semua ponsel, kami dapat menggunakan RATA-RATA rumus seperti di bawah ini:

= AVERAGE (E2: E16)

Hasilnya harus seperti di bawah ini:

Rumus COUNT, SUM, dan AVERAGE hanya akan berfungsi untuk catatan di mana nilai sel dalam format angka. Rekaman apa pun dalam rentang rumus (yaitu. E2: E16 dalam contoh ini) tidak dalam format angka akan diabaikan.

Jadi, harap pastikan bahwa semua sel dalam rumus COUNT, SUM, dan AVERAGE semuanya diformat sebagai Jumlah, tidak Teks. Coba gunakan rumus yang sama, tetapi dengan E: E sebagai rentang alih-alih E2: E16. Ini akan mengembalikan hasil yang sama seperti sebelumnya karena mengabaikan tajuk (yaitu. Harga penjualan), yang dalam format teks.

Sekarang, bagaimana jika kita ingin mengetahui jumlah penjualan, jumlah total penjualan dan jumlah rata-rata penjualan per telepon, hanya untuk yang dijual di AS? Di sinilah COUNTIFS, SUMIFS, dan AVERAGEIFS memainkan peran penting. Perhatikan rumus di bawah ini:

COUNTIF

Rincian rumus:

1. = COUNTIFS ( - Itu “=” menunjukkan awal formula dalam sel dan COUNTIF adalah bagian pertama dari fungsi Excel yang kami gunakan.
2. D2: D16 - Mengacu pada rentang data untuk memeriksa untuk melihat apakah memenuhi kriteria untuk dimasukkan dalam rumus hitungan.
3. "AMERIKA SERIKAT" - Kriteria yang harus dicari dalam rentang data yang ditentukan (D2: D16)
4. ) - Braket penutup menunjukkan akhir formula.

Formula mengembalikan 6 yang merupakan jumlah penjualan untuk produk yang dikirim dari gudang USA.

SUMIFS

Rincian rumus:

1. = SUMIFS ( - Itu “=” menunjukkan awal formula lagi.
2. E2: E16 - Mengacu pada rentang data yang ingin kami totalkan, mis. Harga jual dalam contoh kami.
3. D2: D16 - Mengacu pada rentang data untuk memeriksa untuk melihat apakah memenuhi kriteria untuk dimasukkan dalam jumlah total.
4. "AMERIKA SERIKAT" - Kriteria yang harus dicari dalam rentang data yang ditentukan (D2: D16)
5. ) - Braket penutup menunjukkan akhir formula.

Formula menunjukkan $ 6.050 total penjualan yang dibuat untuk produk yang dikirim dari gudang USA.

AVERAGEIFS

Rincian rumus:

1. = AVERAGEIFS ( - Itu “=” menunjukkan awal formula.
2. E2: E16 - Mengacu pada rentang data yang ingin kami rata-rata. Dalam contoh ini, kami ingin mendapatkan jumlah penjualan rata-rata untuk semua ponsel yang dijual di AS.
3. D2: D16 - Mengacu pada rentang data untuk memeriksa untuk melihat apakah memenuhi kriteria untuk dimasukkan dalam rumus rata-rata.
4. "AMERIKA SERIKAT" - Kriteria yang harus dicari dalam rentang data yang ditentukan
5. ) - Braket penutup menunjukkan ujung formula.

Formula menunjukkan kami menjual produk sekitar $ 1,008 per telepon di AS.

Ketiga formula dapat mengambil lebih dari satu kriteria. Misalnya jika kita ingin mengetahui angka yang sama (mis. MENGHITUNG, JUMLAH dan RATA-RATA) untuk produk yang dijual di Amerika Serikat, tetapi khusus hanya untuk Samsung merek, kita hanya perlu menambahkan rentang data untuk diperiksa diikuti dengan kriterianya.

Silakan lihat contoh di bawah ini di mana kriteria kedua ditambahkan ke pemeriksaan kriteria awal. (Teks biru menunjukkan kriteria pertama dan merah menunjukkan kriteria kedua)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Anda akan melihat bahwa Excel juga memiliki COUNTIF, SUMIF dan AVERAGEIF rumus tanpa akhiran "S". Itu digunakan mirip dengan COUNTIF, SUMIFS dan AVERAGEIFS. Namun, mereka yang tidak memiliki akhiran "S" dalam formula memiliki batasan hanya mengizinkan satu kriteria per formula.

Karena sintaksnya sedikit berbeda, saya akan merekomendasikan menggunakan COUNTIF, SUMIFS dan AVERAGEIFS hanya karena dapat digunakan untuk satu kriteria atau lebih, jika perlu. Nikmati!