SUMIF excel function is used to sum values between a different date range. The cells values are added based on specific criteria or a set of conditions.
SUMIF function is used to sum values on a single criterion.
Syntax
=SUMIF (range, criteria, [sum_range])
The range is the number of cells evaluated based on criteria required.
Criteria – the condition which must be met
Sum_range is the range of cells to sum if the provided condition is met.
Excel SUMIFS FUNCTION is used to sum values using date criteria. The function can be used to sum values with multiple criteria.
Syntax
=SUMIFS (Sum_range, Criteria_range1, Criteria 1, [Criteria_range2, Criteria 2], …)
The function uses two dates as the criteria range and sums all the values between the dates range. The formula allows you to specify more than one criteria and sum their value. The sum is applied to all the cells which meet the specified condition.
Example: To sum values between two dates.
1. Create the following table
2. Add the rows above or below the data cells to display the results of the total between different date ranges.
3. Add the start date and end date as shown above. Specify the criteria code to evaluate the total.
4. On cell D4, enter the following formula
=SUMIFS($D$7:$D$23,$B$7:$B$23,C4,$A$7:$A$23,">="&B3,$A$7:$A$23,"<="&B4)
5. Press Enter key to display the results
6. Once you press enter, the formula is copied to all cells in the worksheet. You can change the criteria code or the start and end dates to display the sum of different product codes.
NOTE: The ampersand (&) symbol is used to concatenate the string. It is used when an excel function is combined with a logical operator in the criteria.
The logical operator used with SUMIFS helps in adding the condition while calculating the sum between the date ranges.
From the formula, cells range D7:D23 is the sum-range for the total amount.
A7:A23 is the first Criteria_range which is used to set up Criteria1 (start date) and Criteria 2 (End date).
The function sums the cell with dates between July 10, 2017, and April 10, 2018, for product code D.
Sum Values between Dynamic Date Ranges
The above function will only sum values that are within the given range. You can also set the functions to dynamically get the sum values of two dates on a continuous basis (every day getting the sum of values for the past 7 days).
In such a situation, the Today () function is used to display the sum on daily basis. The values change automatically based on the current day.
How it works
A. The first criteria sum values equal to or lower than today's date.
B. The second criteria sum only those values lower than or equal to a date which is 6 days prior to today.
C. The range of dates will be from today to six days back.
=SUMIFS(C7:C23,A7:A23,">="&TODAY()-7),A7:A23,"<="&TODAY())
