Calculating Dates

[davros]

Hi,

Really struggling with date ranges in excel, basically i have a spreadsheet to track sales. Sales are entered by entering a date into column G on sheet 1. I then want an overall sales count on sheet 2, which will add up all sales between specific date using the date input in columng G on sheet 1. Does anyone know what formula i can use to search column G and count the amount of dates in this column between a specific date range?

Im finding it impossible.

 

[hyat]

Hey davros.

If you're using Excel 2007 onwards, this might work if you wanted to count, say, the number of dates in column G of Sheet1 falling between Jan 31, 2011 and Feb 15, 2011:

=COUNTIFS(Sheet1!$G:$G,">31/01/2011",Sheet1!$G:$G,"<=15/02/2011")

 

[hyat]

For earlier versions, this might work:

=SUMPRODUCT((Sheet1!$G:$G>DATEVALUE("31/01/2011"))*(Sheet1!$G:$G<=DATEVALUE("15/02/2011")))

You could also input the lower and upper date in two cells of your sheet, say, cells A1 and A2 of Sheet2, and refer to them in the function.

E.g.

=SUMPRODUCT((Sheet1!$G:$G>Sheet2!A1)*(Sheet1!$G:$G<=Sheet2!A2))