Thread: Calculating difference in date between 2 columns of dates

Col a------------------------------------Col b----------------------------------Col c
Destination---------------------------Applied-------------------------------Granted
London-------------------------------19/08/2010---------------------------26/11/2010
New York----------------------------14/05/2010---------------------------15/12/2010
Madrid--------------------------------09/01/2010---------------------------20/10/2010
London-------------------------------09/08/2010---------------------------16/12/2010
New York---------------------------11/02/2010----------------------------20/08/2010
New York---------------------------14/03/2010----------------------------22/11/2010

I have a spreadsheet with 50k rows with dates roughly outlined above. What formula would I need to show the amount of London cases have been granted between 3 and 6 months?

I have seen a formula for this using 2 sumifs. However is there another way ie using just 1 sumif, sumproduct, countif or alternative?

I am using Excel 2003.

Try this,

=SUMPRODUCT((A2:A7="London")*(DATEDIF(B2:B7,C2:C7,"m")>=3)*(DATEDIF(B2:B7,C2:C7,"m")<=6))

Regards

Do you want to know that info for just one destination and timescale band, or for all destinations and all timescale bands? If the latter, a pivot table is probably what you want...