In this example we show you how the SUMIFS-function works in combination with a date. The curious thing is that Excel and GoogleSheets don't save your date as you think they do. They actually store it as a number. Luckily we have the possibility to translate a date into a number: e.g. QA{spreadsheetTime}.
In the example below, you can select a start date and an end date. Perfect for booking forms.
The table contains the date-range from October 1st 2021 to November 5th 2021 in the fist column. You see the date displayed as a number, since we imported the table from GoogleSheets. The second column displays the price for that day.
The SUMIFS-function will sum all prices for the selected date range.
This example will also be available as a template. Go ahead and try it out!
| Su | Mo | Tu | We | Th | Fr | Sa |
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
| 31 |
The first date in the table, October 1st, correlates to the number 44470
| Su | Mo | Tu | We | Th | Fr | Sa |
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
| 31 |
The first date in the table, October 1st, correlates to the number 44470
Pick a starting and ending date between October 1st and November 5th and the prices for that range get added with the SumIFs function.
Show/Hide the reference date table