Tom’s Tutorials for Excel: Count and Sum Between Dates
In the picture, a table contains several columns of data, including a column of dates. Three examples show how you can perform various mathematical operations on multiple-column criteria between dates using the SUMPRODUCT function.
These tasks are also achievable with array formulas but many users prefer not needing to remember to press Ctrl+Shift+Enter, which is why SUMPRODUCT is used for these examples.
Note that although the pictured table shows dates listed in ascending order, these formula examples will work the same way regardless of what order (or disorder) the dates are listed in your table.
In the yellow section for Example 1, the task is to count how many dates during June 2011 show Widgets in Item column B.
Based on the table, the answer is 2.
The solution formula in cell G6 is
=SUMPRODUCT((A2:A17>=DATE(2011,6,1))*(A2:A17<=DATE(2011,6,30))*(B2:B17=F6))
In the green section for Example 2, the task is to sum the total of Wallabies that were sold during July 2011. The formula must find Wallabies as an Item listed in column B between July 1, 2011 and July 31, 2011 and sum for each found Item's Unit Count in column C.
Based on the table, the answer is 23.
The solution formula in cell G11 is
=SUMPRODUCT((A2:A17>=DATE(2011,7,1))*(A2:A17<=DATE(2011,7,31))*((B2:B17=F11)*(C2:C17)))
In the pink section for Example 3, the task is to calculate gross sales of Wombats in August 2011. Notice that a field for total gross sales does not need to exist in the source table for you to be able to calculate that number, based on information in the other existing fields. The SUMPRODUCT function finds Wombats in the Item column B during August, multiplies each by its Unit Count in column C, each multiplied product by its Unit Price in column D, and sums each of those multiplied products for the final answer.
Based on the table, the answer is 249.
The solution formula in cell G17 is
=SUMPRODUCT((A2:A17>=DATE(2011,8,1))*(A2:A17<=DATE(2011,8,31))*((B2:B17=F17)*(C2:C17)*(D2:D17)))
Need your help sir to calculate the minute variance between two times I.e. 23:40 to 00:05, this is challengeing for me as after 00:00 hrs date has been change.
If 23:40 is in cell A1 and 00:05 is in cell B1, the formula
=MOD(B1-A1,1)
will return 0:25 which is 25 minutes and the correct answer.
If you see the formula return 0.017361 that is still the correct answer. To make it look like 0:25 you need to custom format the cell as h:mm