How to Calculate Year-to-Date (YTD) Totals in Google Sheets

Calculating Year-to-Date (YTD) totals is essential for corporate financial reporting, sales tracking, and operational analytics. A dynamic YTD formula aggregates metrics beginning on the first day of the current calendar or fiscal year up to today’s date. While static sums require periodic manual updates to expand date boundaries, Google Sheets offers several dynamic approaches that update figures automatically as new daily transactions are entered.

By pairing the SUMIFS function with dynamic date functions like TODAY, DATE, and YEAR, you can build self-updating formulas that remain accurate regardless of the current date.

Calculating Calendar Year-to-Date Totals with SUMIFS

The most direct and efficient method to compute a YTD total in Google Sheets is using the multi-criteria SUMIFS function.

Assuming your transaction dates reside in Column A (e.g. A2:A1000) and sales revenue figures reside in Column B (e.g. B2:B1000), apply the following formula:

=SUMIFS(B2:B1000, A2:A1000, ">="&DATE(YEAR(TODAY()), 1, 1), A2:A1000, "<="&TODAY())

How this formula operates:

• B2:B1000: Specifies the range of values to sum.
• A2:A1000, ">="&DATE(YEAR(TODAY()), 1, 1): The first condition checks that the date is on or after 1 January of the current year. YEAR(TODAY()) dynamically returns the current year number.
• A2:A1000, "<="&TODAY(): The second condition ensures only transactions up to and including the current day are factored into the calculation, filtering out future forecasts or post-dated entries.

Calculating Fiscal Year-to-Date (FYTD) Totals

If your organisation operates on a non-calendar fiscal year (for example, starting on 1 April or 1 July), you can adjust the formula dynamically to reflect your fiscal start date.

For a fiscal year commencing on 1 April:

=SUMIFS(B2:B1000, A2:A1000, ">="&DATE(IF(MONTH(TODAY())>=4, YEAR(TODAY()), YEAR(TODAY())-1), 4, 1), A2:A1000, "<="&TODAY())

This logical structure automatically checks whether the current month is in or after April. If so, it adopts the current calendar year as the starting point; otherwise, it steps back to the previous calendar year.

Using the FILTER Function for Complex Dynamic YTD Metrics

If you need to calculate averages, medians, or counts over a YTD window, wrapping a FILTER function inside aggregation formulas is highly flexible:

=SUM(FILTER(B2:B1000, A2:A1000 >= DATE(YEAR(TODAY()), 1, 1), A2:A1000 <= TODAY()))

To compute the average transaction size YTD instead of the total, simply replace SUM with AVERAGE. For further analytical techniques, see our guide on how to calculate the median value in Google Sheets using the MEDIAN function.

Troubleshooting Date Calculation Errors

If your YTD formulas return 0 or an unexpected sum, check the following common issues:

• Dates formatted as text: Google Sheets cannot evaluate textual date strings against numeric dates. Ensure Column A is formatted as true dates by navigating to Format > Number > Date.
• Time stamps in date cells: If your date column includes time values (such as 2026-10-30 14:35:00), comparing against TODAY() at midnight may exclude transactions that occurred later in the current day. To capture all entries through the end of today, use "<"&(TODAY()+1).
• Blank rows or header contamination: Ensure that your criteria range and sum range have identical row dimensions (e.g., both starting on row 2) to prevent calculation errors.