Sum By Month to Date or Year to Date- Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 7, 2023

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the SUMIFS function to sum data month-to-date or year-to-date in Excel and Google Sheets.

Sum If Month Year to Date in Excel

Sum by Month to Date

The SUMIFS Function sums data for rows that meet certain criteria. Its syntax is:

This example sums Table Reservations month-to-date, using the Functions: SUMIFS, DATE, YEAR, MONTH, and TODAY.

=SUMIFS(C3:C11,B3:B11,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B3:B11,"<="&TODAY())

SUMIFS by Month to Date

Now let’s step through the formula.

TODAY Function

The TODAY Function returns the current date. The value is recalculated each time the file is viewed.

=TODAY()

SUMIFS by Month to Date Calcs1

This is the upper end of our date range for the formula.

DATE Function

Then calculate the lower end of the date range: the first day of the current month. We use the DATE Function with YEAR and MONTH to produce this date value. Use the year and month of today’s date and set day to 1.

=DATE(YEAR(E3),MONTH(E3),1)

SUMIFS by Month to Date-Calcs2

SUMIFS Function

Use the SUMIFS Function to sum Table Reservations when the date is greater than or equal to the first day of the month and less than or equal to the current day.

=SUMIFS(C3:C11,B3:B11,">="&F3,B3:B11,"<="&E3)

SUMIFS by Month to Date Calcs3

Combining these steps gives us the formula for the month to date sum:

=SUMIFS(C3:C11,B3:B11,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),B3:B11,"<="&TODAY())

SUMIFS by Month to Date

Sum by Year to Date

The same logic can be used to sum the number of Table Reservations for the current year up until today by making the start date the first day of the year. Both month and day are set to 1.

=SUMIFS(C3:C11,B3:B11,">="&DATE(YEAR(TODAY()),1,1),B3:B11,"<="&TODAY())

SUMIFS by Year to Date

Locking Cell References

To make our formulas easier to read, we’ve shown them without locked cell references:

=SUMIFS(C3:C11,B3:B11,">="&DATE(YEAR(TODAY()),1,1),B3:B11,"<="&TODAY())

But these formulas will not work properly when copy and pasted elsewhere with your Excel file. Instead, you should use locked cell references like this:

=SUMIFS($C$3:$C$11,$B$3:$B$11,">="&DATE(YEAR(TODAY()),1,1),$B$3:$B$11,"<="&TODAY())

SUMIFS by Year to Date$

Read our article on Locking Cell References to learn more.

Sum by Month to Date or Year to Date in Google Sheets

These formulas work exactly the same in Google Sheets as in Excel.

sum if month year to date Google Function