Date Search Filter for calculating total values across sheets in google sheet

Google Sheets is a powerful tool for managing and analyzing data, but working with large amounts of information can be challenging. One common task is calculating the total value of data that meets certain criteria, such as all sales made in a specific time period. While Google Sheets has a built-in filter feature to search for data based on certain values, it can be time-consuming to apply filters across multiple sheets. In this article, we will explore how to use a date search filter in Google Sheets to efficiently calculate total values across multiple sheets.

Here I took two column at column A I have mentioned some labels like start date, end date and total expenses. In second column we have set some value for filtering our data to calculate our monthly expenses.

Let us explain below google sheet formula for date filter between two dates to calculate your expenses.

This formula calculates the sum of values in column C of a sheet named “Transactions” that meet the following conditions:

1.The corresponding values in column B (of the same row) are greater than or equal to the value in cell B1 of a sheet named “Research“.

2.The corresponding values in column B (of the same row) are less than or equal to the value in cell B2 of the “Research” sheet.

Here is a breakdown of the formula:

The filter() function is used to retrieve only the rows from the “Transactions” sheet that meet the specified conditions.

Transactions!$C:$C is the range of values in column C of the “Transactions” sheet that we want to sum.

Transactions!$B:$B>=Research!$B1 specifies the first condition. It checks if the values in column B of the “Transactions” sheet are greater than or equal to the value in cell B1 of the “Research” sheet.

Transactions!$B:$B<=Research!$B2 specifies the second condition. It checks if the values in column B of the “Transactions” sheet are less than or equal to the value in cell B2 of the “Research” sheet.

Finally, the sum() function is used to add up all the values that pass the above conditions.

Note that the dollar signs ($) in the formula indicate absolute referencing, which means that the formula will always refer to the same columns and cells, even if it is copied or moved to another location within the sheet.

If you want to see my data analytics excel parts tutorial you can check our playlists here. You can check our another article about EOMONTH method (End of the month).