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
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
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