Table of Contents
COUNTIFS is a powerful function in Google Sheets that can be used to count the number of cells meeting multiple criteria in a given range. It is especially useful when used with a date range, as it can count the number of cells within a specified date range. This is done by adding two criteria to the COUNTIFS function, with the first criteria being the range of dates and the second criteria being the cell range you wish to count. This can be used to count the number of instances within a certain date range, allowing for more accurate data analysis.
You can use the following syntax to count the number of cell values that fall in a date range in Google Sheets:
=COUNTIFS(A2:A11,">="&D2, A2:A11,"<="&E2)
This formula counts the number of cells in the range A2:A11 where the start date is equal to or greater than the date in cell D2 and the end date is equal to or less than the date in cell E2.
The following example shows how to use this syntax in practice.
Example: Use COUNTIFS with Date Range in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the number of products sold on various days:
We can define a start and end date in cells D2 and E2 respectively, then use the following formula to count how many dates fall within the start and end date:
=COUNTIFS(A2:A11,">="&D2, A2:A11,"<="&E2)
The following screenshot shows how to use this formula in practice:
We can see that 3 days fall in between the range 1/10/2021 and 1/15/2021.
We can manually verify that the following three dates in column A fall in this range:
- 1/12/2021
- 1/14/2021
- 1/15/2021
If we change either the start or end date, the formula will automatically update to count the cells within the new date range.
For example, suppose we change the start date to 1/1/2021:
The following tutorials provide additional information on how to work with dates in Google Sheets: