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