Google Sheets Project | FILTER | DATE | SUM | Calculate Total Cost of Item Ordered Between Two Dates
In Google Sheets, you can calculate the total cost of items ordered
between two dates easily. A solution for this task is to use FILTER, DATE,
and SUM functions. FILTER extracts data based on one or more conditions.
DATE function converts specified year, month, and day into a date. SUM
adds numbers in one or more ranges.
If you wish, you can nest FILTER inside SUM to extract the costs, based
on the specified conditions(two dates, in this case), and add the costs
to get the total cost in one step.
-------------------------------------
Google Sheets Quiz with Demo of Answers
Try this 5-question quiz, which also demos the answers. The quiz
can be helpful in your day-to-day spreadsheet work or for job interview:
• Google Sheets Functions Quiz | Fill-i...
-------------------------------------
FILTER Function
The link below gives the steps to use the FILTER function to extract
data based on one or more conditions:
• Google Sheets Filter Function | Extra...
-------------------------------------
DATE Function
Here is the link to the step-by-step tutorial for using the DAY
function to convert month, day, and year into a date:
• Google Sheets DATE Function | DAYS Fu...
-------------------------------------
SUM Function
The link to the step-by-step tutorial on SUM function is:
• How to Sum in Google Sheets | Without...
-------------------------------------
In this project, we will be using three functions: DATE, FILTER, and SUM.
1. DATE Function
Here is the format of DATE:
=DATE(year, month, day)
For a step-by-step video tutorial on how to use DATE, please review the separate tutorial on DATE. The link to this tutorial is given above.
First, the user has to simply enter the year, month, and day, each
in a separate cell.
Use the DATE function to convert the year, month, day into a date in the
mm/dd/yyyy format. For each of the arguments of the DATE function, input the relevant cell address.
2. FILTER Function
The FILTER function returns data based on one or more conditions.
Let's use FILTER to extract the cost of items ordered between two dates.
Let's look at the format of FILTER:
=FILTER(range, condition1, [condition2], […])
Please review the separate step-by-step video tutorial on FILTER for more info. The link to this tutorial is given above.
For range, specify the Cost column.
For condition1, specify the first date as follows: Specify the date range, type the greater-than-or-equal-to symbol, and type the DATE function to convert year, month, and day entered by user, for the start date, into a date.
For condition2, specify the second date as follows: Specify the same date range, type the less-than-or-equal-to symbol, and type the DATE function to convert year, month, and day entered by user, for the end date, into a date.
Example:
Let the cost range be cells E3 to E11 and Date range be cells B3 to
B11 of the spreadsheet.
Assume that start year, month, and day are entered in cells H3, I3, and J3
respectively. Further assume that end year, month, and day are entered in
cells H4, I4, and J4 respectively.
Nest the DATE function inside FILTER.
Say that FILTER returns the following costs 128900, 60076, and 375074, as
these are the cost of items ordered between and including the two dates.
3. SUM Function
The SUM function returns the sum of numbers in one or more ranges.
Here is the format of the SUM function formula:
=SUM(value1, [value2], […])
If you wish to know more about the SUM function, please review the separate
video tutorial on SUM. The link to the tutorial is given above.
For value1, substitute the FILTER function given above.
Nest the FILTER function inside SUM:
SUM will return the total cost of Rs. 564050 (this is the sum of numbers
returned by FILTER 128900, 60076, and 375074).
Review this video tutorial, which gives the steps for using the Google Sheets FILTER, DATE, and SUM functions to calculate the total cost of items ordered between two dates, with an example.