sum if date is greater

Sum if date is greater than a specified date. In this example, we will use the following formula to sum a range if the date is past our specified date. We will use the SUMIF Function for a single criteria match the SUMIFs Function for multiple criteria. Let’s review the formula.

Formula using SUMIF Function – Single Criteria

=SUMIF(range,"Logical Operator >"&DATE(year,month,day),sum_range)
  • range – Range of cells that you want to apply the criteria against.
  • logical operator – “=”,”>”,”>=”, “>”,”<”
  • date – enter a valid date
  • sum_range – Specifies the cells to add together. If the sum_range is omitted, the cells in range are added together instead.

Sum if Date is greater Example

Let’s review the following data set. We want to use the SUMIF function to sum the sales for all material after October 1, 2019.

Data Data

To accomplish this, we will use the following formula using the SUMIF Function.

We begin by defining our range, A8:A30. We then add our logical operator “>”. This can be any type of valid operator “=”,”>”,”>=”, “>”,”<” etc.

The critical part to our formula is adding the &DATE(Year,Month,Date). Finally, we select the column to sum, C8:C30.

=SUMIF(range,criteria,[sum_range]) 
=SUMIF(A8:A30,">"&DATE(2019,10,1),C8:C30)

Pay close attention when using this formula. It can be easily broken if it is not correctly typed into Excel. However, this can be extremely useful when quickly summing up large quantities of data outside of Pivot Tables.

Sum if date is greater than

SUMIFS Date Example – Multiple Criteria

Let’s now use the SUMIFS function to demonstrate the same example above, but with multiple criteria. In this scenario, we will be using the following formula.

=SUMIFS(C5:C28,A5:A28,">"&DATE(2019,10,1),B5:B28,F13)

We want to find the sales past October 1, 2019 for each individual material. To find the sales for Material XY1B, we will use this formula.

=SUMIFS(C5:C28,A5:A28,">"&DATE(2019,10,1),B5:B28,F15)
SUMIFS greater than date

Sum if Greater than with Named Ranges

You can also use named ranges to make the formula easier to follow. This is done by first naming your data sets. Review the following tutorial for a better understanding.

=SUMIFS(sales,date,">"&DATE(2019,10,1),material,F13)
sumif greater than named range