Hitungan google sheets jika lebih besar dari tanggal

Hitung nilai antara dua tanggal atau rentang tanggal adalah topik yang luas. Bergantung pada tujuan kita, kita dapat menggunakan berbagai fungsi atau kombinasi di Google Sheets

Anda mungkin ingin menghitung semua atau nilai tertentu dalam rentang tanggal. Mungkin ada lebih dari dua kolom atau baris

Bergantung pada itu, kami memilih fungsi atau kombinasi kami

Mari saya mulai dengan contoh dasar di mana saya akan menggunakan COUNITFS untuk memenuhi tujuan saya

Rumus Dasar Menghitung Nilai Antara Dua Tanggal

Saya ingin menghitung jumlah “P” (sekarang) pada rentang tanggal 11/01/2021 dan 15/11/2021

Tanggal ada di kolom A, dan status (“P” ) ada di kolom B

Di sini kami ingin menghitung satu nilai spesifik antara dua tanggal di Google Sheets

Rumus

=countifs(B2:B,"P",A2:A,">="&date(2021,11,1),A2:A,"<="&date(2021,11,15))

Pada rumus di atas, kriterianya adalah tanggal 11/1/2021, 15/11/2021, dan string “P. ”

Kami dapat menentukannya dalam sel dan menggunakan rumus seperti yang diberikan di bawah ini

=countifs(B2:B,D5,A2:A,">="&E2,A2:A,"<="&E3)

Untuk menghitung semua nilai antara dua tanggal, Anda perlu mengganti bagian yang dicetak tebal pada rumus di atas, yaitu. e. , “P” atau D5, dengan wildcard “*” (tanda bintang)

=countifs(B2:B,"*",A2:A,">="&E2,A2:A,"<="&E3)

Tantangan sebenarnya datang ketika nilai tersebar di beberapa baris atau kolom

Berikut adalah dua contoh

Rumus Kombinasi untuk Menghitung Nilai Antara Dua Tanggal

Saya memiliki tabel yang berisi catatan log panggilan pelanggan di Google Sheets

Tujuan saya adalah membuat laporan ringkasan berdasarkan rentang tanggal tertentu

Saya ingin mengetahui berapa kali saya menelepon pelanggan selama satu periode (formula non-array). Ini dapat membantu saya memahami bagaimana saya menindaklanjuti rilis pembayaran

Saya mungkin juga ingin membuat laporan ringkasan log panggilan pelanggan selama periode tertentu (formula larik)

Singkatnya, saya ingin menghitung string teks antara rentang tanggal di Google Sheets

Kami dapat mengatur jenis laporan seperti itu secara vertikal atau horizontal

Berdasarkan hal tersebut, rumus untuk menghitung nilai antara dua tanggal di Google Sheets dapat bervariasi

Struktur Tabel Vertikal – Rumus Array atau Non-Array

Dalam struktur ini, tanggal ada di kolom pertama tabel, dan nama pelanggan ada di kolom berikutnya

Berikut adalah contoh di mana log panggilan berada dalam rentang sel A1. G, dan hasil yang saya harapkan berada di kisaran sel O2. P6

Abjad A, B, C, D, dan Y mewakili nama pelanggan

Rumus Non-Larik untuk Menghitung Nilai dalam Rentang Tanggal

=countif(
     filter($B$2:$G,$A$2:$A>=$L$1,$A$2:$A<=$L$2),
     O2
)

Masukkan kombinasi COUNTIF dan FILTER di atas di sel P2. Salin ke P3. rentang P6

Ini adalah rumus COUNTIF sesuai sintaks COUNTIF(range, criterion)

Rentangnya adalah rumus FILTER, dan kriterianya ada di sel O2, yaitu nama pelanggan

Apa yang dilakukan FILTER dalam rumus ini?

Ini memfilter baris

Sintaksis. FILTER(range, condition1, [condition2, …])_

Jangkauan. B2. G

Kondisi1. $A$2:$A>=$L$1_

Kondisi2.

=countifs(B2:B,D5,A2:A,">="&E2,A2:A,"<="&E3)

Filter memfilter nama pelanggan dalam rentang B2. G jika tanggal di A2. Jatuh antara tanggal di L1 dan L2

COUNTIF menggunakan rentang ini untuk menghitung pelanggan di O2

Ketika kami menyeret formula ini ke bawah, nama pelanggan berubah karena kami telah menggunakan referensi sel O2, yang relatif direferensikan (bukan referensi absolut $O$2)

Rumus Array untuk Menghitung Nilai dalam Rentang Tanggal

=Query(
     flatten(
        filter(B2:G,A2:A>=L1,A2:A<=L2)
     ),
     "Select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''"
)

Masukkan kombinasi QUERY, FLATTEN, dan FILTER di atas dalam sel O2 (kisaran O2. P harus dikosongkan sebelumnya)

Saat kami menggunakan rumus array untuk menghitung nilai antara dua tanggal di Google Sheets, kami hanya diminta untuk menentukan tanggal mulai dan berakhir di L1. L2

Di sini, tidak perlu menentukan nama pelanggan di O2. O6

Di sini juga, FILTER memfilter nama pelanggan yang termasuk dalam periode tertentu

Akan ada beberapa kolom di output. FLATTEN mengatur keluaran itu menjadi satu kolom

Fungsi QUERY mengelompokkan nama pelanggan dan mengembalikan jumlah

Struktur Tabel Horizontal – Rumus Larik atau Non-Larik

Dalam struktur ini, tanggal berada di baris pertama tabel, dan nama pelanggan berada di baris berikutnya

Ini berguna saat kita ingin menghitung nilai (string teks atau angka) di bawah deretan tanggal berdasarkan rentang tanggal

Berikut adalah contoh di mana log panggilan berada dalam rentang sel A1. U, dan hasil yang saya harapkan ada di rentang sel W5. X9

Ini sebenarnya adalah versi data sampel kami sebelumnya yang dialihkan

Kita dapat menggunakan rumus non-array di bawah ini untuk menghitung nilai spesifik antara dua tanggal dalam data yang disusun secara horizontal di Google Sheets

=countif(
     filter($B$2:$U,$B$1:$U$1>=$X$2,$B$1:$U$1<=$X$3),
     W5
)

Ini mirip dengan kombo COUNTIF dan FILTER kami sebelumnya. Masukkan di sel X5 dan salin ke X6. X9

Anda harus menentukan kondisi yang diperlukan di sel X2. X3 (tanggal mulai dan berakhir) dan W5. W9 (nama pelanggan)

Apa peran FILTER dalam formula ini?

Ini memfilter kolom

Filter menyaring B2. U (nama pelanggan) jika tanggal di A1. U1 berada di antara tanggal yang diberikan di X2 dan X3

Kita dapat menggunakan rumus array di bawah ini untuk mengelompokkan semua nilai antara dua tanggal di Google Sheets

Bagaimana Anda menghitung sel yang lebih besar dari tanggal?

Bagaimana cara menggunakan Countif di Google Sheets dengan tanggal?

Bisakah Anda menggunakan Countif dengan tanggal?

Bagaimana cara menggunakan Countif dengan kriteria lebih besar dari?