We use SUMIFS function many times in our daily professional life. Did you try anything about sum a date range based on Date, where date in formula you need to use as reference cell? I'm sure you did but can't remember. Okey, here is a cool trick about how to use it as mentioned as above.
How to use the formula:
First of all, follow the below sample data table:
Think that, you need to SUM the Numbers in between 4-May to 15-June. And this date can be changed anytime. So, it is wise to use a reference cell like D3, to just change the date and the result will automatically shown. Now time to format your report:
Enter the Start_Date as 4-May-17 and End_Date as 15-Jun-17. In F3 cell enter the below formula:
The & symbol used before cell reference.
The result will show like below:
How to use the formula:
First of all, follow the below sample data table:
Image 1: Sample data table
Think that, you need to SUM the Numbers in between 4-May to 15-June. And this date can be changed anytime. So, it is wise to use a reference cell like D3, to just change the date and the result will automatically shown. Now time to format your report:
Image 2: Designed the format for calculation SUMIFS
Enter the Start_Date as 4-May-17 and End_Date as 15-Jun-17. In F3 cell enter the below formula:
=SUMIFS($B$2:$B$21,$A$2:$A$21,">="&$D3,$A$2:$A$21,"<="&$E3)
The & symbol used before cell reference.
The result will show like below:
Image 3: The result of SUMIFS