Formula umum
=COUNTIFS(rng1,1,rng2,">="&A1,rng2,"<="&EOMONTH(A1,0))
Ringkasan
Untuk menghitung pelanggan baru berdasarkan bulan, Anda dapat menggunakan kolom pembantu dan fungsi COUNTIFS. Pada contoh yang ditampilkan, rumus di H5 adalah:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
di mana "baru" (E5: E15), dan "tanggal" (C5: C15) diberi nama rentang.
Penjelasan
Rumus ini bergantung pada kolom pembantu, yaitu kolom E dalam contoh yang diperlihatkan. Rumus dalam E5, disalin, adalah:
=(COUNTIFS($B$5:B5,B5)=1)+0
Rumus ini mengembalikan 1 untuk pelanggan baru dan 0 untuk pelanggan tetap, dan dijelaskan secara rinci di sini. Setelah rumus ini diterapkan, fungsi COUNTIFS dapat digunakan untuk menghitung pelanggan baru di setiap bulan.
Rentang dan kriteria pertama di dalam COUNTIFS menghitung 1 di kolom "baru":
=COUNTIFS(new,1
tanpa kriteria lebih lanjut, ini akan mengembalikan jumlah semua pelanggan unik dalam data. Namun, kami ingin menghitung per bulan, jadi kami perlu membatasi hitungan untuk setiap bulan yang ditunjukkan di kolom G.
Nama bulan di kolom G sebenarnya adalah tanggal "bulan pertama": 1-Jan-2019, 1-Feb-2019, dan 1-Mar-2019. Tanggal diformat dengan format nomor pelanggan "mmm" untuk ditampilkan sebagai nama bulan 3 huruf: Ini memungkinkan kita untuk menulis kriteria sederhana untuk menghitung berdasarkan bulan menggunakan tanggal secara langsung.
Untuk membatasi hitungan ke hanya 1 yang lebih besar dari atau sama dengan bulan pertama di kolom G kami menggunakan rentang bernama "tanggal" yang digabungkan ke operator lebih besar dari atau sama dengan:
=COUNTIFS(new,1,date,">="&G5
Untuk membatasi penghitungan lebih lanjut agar hanya menyertakan 1 yang terjadi pada akhir setiap bulan, kami menambahkan satu pasangan rentang / kriteria terakhir:
=COUNTIFS(new,1,date,">="&G5,date,"<="&EOMONTH(G5,0))
Di sini sekali lagi kami menggunakan rentang bernama "tanggal", dan kami menggabungkan kurang dari atau sama dengan operator (<=) ke hari terakhir bulan itu, dibuat dengan fungsi EOMONTH.
Saat rumus ini disalin, ini mengembalikan jumlah pelanggan baru di setiap bulan.
Pelanggan berulang per bulan
Untuk menghitung pelanggan tetap menurut bulan, Anda dapat menyesuaikan rumus untuk menghitung nol, bukan 1. Rumus di I5, disalin, adalah:
=COUNTIFS(new,0,date,">="&G5,date,"<="&EOMONTH(G5,0))
