How to calculate a sum in a period of time where dates are involved
One of our students asked this question on Facebook: I would like to know how to calculate a sum in a period of time where dates are involved. This interesting problem can be solved either using the DSUM function or Array formulas. The DSUM involves:
- Copying the headers to a location below the data leaving a space 3-4 rows
- Defining the condition or criteria under the labels or headers
- The headers and the conditions become the ‘criteria’ of the DSUM function
- For a range of dates you can copy the ‘dates’ header twice so that under the first header you define the start date and in the other ‘Dates’ header you define the End date as shown in the training video.
The use of the sum and if functions as an array are quite powerful and useful. Here you need to do the following:
- The data under the headers is selected and given a name. This data then becomes the named range. For example the data under the Dates header is selected and given the name ‘dates’ and the data under the ‘Values’ header is given the name ‘values’.
- Now you write the formula in any cell where you wish to get the result (E5 or E6): =sum(if((Month=(dates)=6), values,0)). Then press the 3 keys ctrl+shift+enter simultaneously from the keyboard to get the result for June. In case you don’t do this correctly you’ll get the result ‘#value’!
Watch the video below to see how to use the dsum and array formulas to solve the problem: