Counting values between two dates or date ranges is a broad topic. We can use different functions or combinations in Google Sheets depending on our purpose.

You may want to count all or specific values in a date range. There may be more than two columns or rows.

Depending on that, we choose our functions or combinations.

Let me start with a basic example in which I will use the COUNITFS to meet my goal.

Basic Formula to Count Values Between Two Dates

I want to count the number of “P” (present) in the date range 01/11/2021 and 15/11/2021.

The dates are in column A, and the statuses (“P” ) are in column B.

Here we want to count one specific value between two dates in Google Sheets.

Formula:

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

In the above formula, the criteria are dates 1/11/2021, 15/11/2021, and the string “P.”

We can specify them in cells and use the formula given below.

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

Related:- Countifs with Isbetween in Google Sheets (for similar use).

To count all the values between two dates, you need to replace the bold part in the above formulas, i.e., “P” or D5, with the wildcard “*” (asterisk).

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

The real challenge comes when the values are spread across several rows or columns.

Here are two examples.

Combination Formulas to Count Values Between Two Dates

I have a table containing the records of customer call logs in Google Sheets. Please scroll down and see the screenshot below.

I aim to create a summary report based on a given date range.

I want to find out how many times I have called a customer during a period (non-array formula). It can help me understand how I am following up on a payment release.

I may also want to create a summary report of the customer call log during a given period (array formula).

In short, I wish to count text strings between a date range in Google Sheets.

We can arrange such types of reports vertically or horizontally.

Based on that, the formula to count values between two dates in Google Sheets may vary.

Vertical Table Structure – Array or Non-Array Formulas

In this structure, the dates are in the first column of the table, and the customer names are in subsequent columns.

Here is a sample in which the call logs are in cell range A1:G, and my expected results are in cell range O2:P6.

The alphabets A, B, C, D, and Y represents customer names.

Non-Array Formula to Count Values in a Date Range

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

Insert the above COUNTIF and FILTER combination in cell P2. Copy it to the P3:P6 range.

It’s a COUNTIF formula as per the syntax COUNTIF(range, criterion).

The range is the FILTER formula, and the criterion is in cell O2, which is the customer name.

What does the FILTER do in this formula?

It filters rows!

Syntax: FILTER(range, condition1, [condition2, …])

Range: B2:G

Condition1: $A$2:$A>=$L$1

Condition2: $A$2:$A<=$L$2

The Filter filters the customer names in the range B2:G if the dates in A2:A fall between the dates in L1 and L2.

The COUNTIF uses this range to count the customer in O2.

When we drag this formula down, the customer name changes as we have used cell reference O2, which is relatively referenced (not $O$2 absolute reference).

Array Formula to Count Values in a Date Range

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

Insert the above QUERY, FLATTEN, and FILTER combination in cell O2 (the range O2:P must be emptied beforehand).

When we use an array formula to count values between two dates in Google Sheets, we are only required to specify the start and end dates in L1:L2.

Here, no need to specify the customer names in O2:O6.

Here also, the FILTER filters the customer names that fall in the given period.

There will be multiple columns in the output. The FLATTEN arranges that output into a single column.

The QUERY function groups the customer names and returns the count.

Horizontal Table Structure – Array or Non-Array Formulas

In this structure, the dates are in the first row of the table, and the customer names are in subsequent rows.

It’s useful when we count values (text strings or numbers) below a row of dates based on a date range.

Here is a sample in which the call logs are in cell range A1:U, and my expected results are in cell range W5:X9.

It’s actually a transposed version of our earlier sample data.

We can use the below non-array formula to count specific values between two dates in horizontally arranged data in Google Sheets.

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

It’s similar to our earlier COUNTIF and FILTER combo. Insert it in cell X5 and copy it to X6:X9.

You must specify the necessary conditions in cells X2:X3 (start and end dates) and W5:W9 (customer names).

What’s the role of the FILTER in this formula?

It filters columns!

The filter filters B2:U (customer names) if dates in A1:U1 are between the given dates in X2 and X3.

We can use the below array formula to group-wise count all the values between two dates in Google Sheets.

=query(
     flatten(
        filter(B2:U,B1:U1>=X2,B1:U1<=X3)
     ),
     "Select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''"
)

Empty W5:X and insert it in W5.

That’s all about how to count all or specific values in a date range in Google Sheets.

Thanks for the stay. Enjoy!

Related Resources

• COUNTIF to Count by Month in a Date Range in Google Sheets.
• Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets.
• How to Include a Date Range in SUMIFS in Google Sheets.
• Count Rows between Two Values in Google Sheets.
• COUNTIFS in a Time Range in Google Sheets [Date and Time Column].
• Conditionally Lookup Dates in Date Range in Google Sheets (Array Formula).