You can use the following formula to sum all values in one column in Excel where the date in a corresponding column is less than a specific date:
=SUMIF(A2:A11, "<"&E1, B2:B11)
This particular formula calculates the sum of values in the range B2:B11 only where the corresponding cells in the range A2:A11 are before the date in cell E1.
The following example shows how to use this formula in practice.
Example: How to Use SUMIF Before Date in Excel
Suppose we have the following dataset that shows the number of sales made on various dates at some company:
Suppose we would like to calculate the sum of all sales that occurred before 4/10/2023.
We can specify this cutoff date in cell E1 and then type the following formula into cell E2 to calculate the sum of all sales that occurred before this date:
=SUMIF(A2:A11, "<"&E1, B2:B11)
The following screenshot shows how to use this formula in practice:
The formula returns a value of 36, which represents the sum of all sales made before 4/10/2023.
We can verify this is correct by manually calculating the sum of all sales before this date:
Sum of Sales before 4/10/2023: 4 + 7 + 7 + 6 + 12 = 36
This matches the value calculated by our formula.
Note that if we change the cutoff date in cell E1, our formula will automatically update to show the sum of sales before the new date.
For example, suppose we change the cutoff date to 2/10/2023:
The formula now returns a value of 24, which represents the sum of all sales made before 2/10/2023.
We can verify this is correct by manually calculating the sum of all sales before this date:
Sum of Sales before 2/10/2023: 4 + 7 + 7 + 6 = 24
This matches the value calculated by our formula.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count by Month in Excel
How to Count by Year in Excel
How to Calculate a Cumulative Sum by Date in Excel
