Fomrula for unique records between 2 dates

JimetyBob

New Member
Joined
Nov 23, 2014
Messages
7
Hi, I have a spreadsheet with the following type of information but is 2000 rows:

CustomerDateLead Time
A01/01/20141
A04/01/20143
B05/01/20141
C04/02/20143
A05/02/20145

<colgroup><col span="3"></colgroup><tbody>
</tbody>

I would like a formula to return number of customers including and between 1/1/14 and 31/1/14 so would expect the result 2 for the sample above.

Also I would like return min lead time for the same time period so would expect this to return 1.

Ive been racking my brain for days and trying lots of examples but keep failing so any help is greatly appreciated. Thanks.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
E1: 1-Jan-2014

F1: 31-Jan-2014

G1, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0)))),
  ROW($A$2:$A$6)-ROW($A$2)+1),1))

H1, control+shift+enter, not just enter:
Rich (BB code):
=IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,$C$2:$C$6)))),"")
 
Upvote 0
E1: 1-Jan-2014

F1: 31-Jan-2014

G1, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0)))),
  ROW($A$2:$A$6)-ROW($A$2)+1),1))

H1, control+shift+enter, not just enter:
Rich (BB code):
=IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,$C$2:$C$6)))),"")

Thank you very much these work perfectly well..

On the second formula some return as blank where I have a couple of blank rows of data in the lead time column is it possible to get this to ignore blanks in the range $C$2:$C$6?
 
Upvote 0
Thank you very much these work perfectly well..

You are welcome.

On the second formula some return as blank where I have a couple of blank rows of data in the lead time column is it possible to get this to ignore blanks in the range $C$2:$C$6?

I suppose you mean...
Rich (BB code):
=IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,IF(ISNUMBER($C$2:$C$6),$C$2:$C$6))))),"")
which must also be confirmed with control+shift+enter?
 
Upvote 0
Yes I did use control+shift+enter, i forgot to mention some of the array for column B is also blank and are the same rows where column C is blank.
 
Upvote 0
Would you post a relevant small sample along with the lead time you want to see?

Avante Item CodeAccount NumberCustomerDate InDue DateDate OutLead Time (Working Days)Turnaround Time (Working Days)
270700.10078.032270700A02-Jan-1402-Jan-1402-Jan-1400
270700.61721.002270700A02-Jan-1402-Jan-1402-Jan-1400
270700.61721.004270700A02-Jan-1402-Jan-1402-Jan-1400
270700.62256.007270700A02-Jan-1402-Jan-1402-Jan-1400
270700.62256.002270700A02-Jan-1402-Jan-1402-Jan-1400
270700.62256.006270700A02-Jan-1402-Jan-1402-Jan-1400
270700.62256.008270700A02-Jan-1402-Jan-1402-Jan-1400
372100.11994.011372100B02-Jan-1402-Jan-1402-Jan-1400
320700.61806.005320700C02-Jan-1402-Jan-1402-Jan-1400
320700.61806.006320700C02-Jan-1402-Jan-1402-Jan-1400
320700.61806.007320700C02-Jan-1402-Jan-1402-Jan-1400
320700.62061.003320700C02-Jan-1402-Jan-1402-Jan-1400
320700.62061.004320700C02-Jan-1402-Jan-1402-Jan-1400
320700.62061.005320700C02-Jan-1402-Jan-1402-Jan-1400
320700.60156.003A320700C02-Jan-1402-Jan-1402-Jan-1400
371000.61015.005371000C03-Jan-1403-Jan-1403-Jan-1400
371000.61016.007371000C03-Jan-1403-Jan-1403-Jan-1400
270700.10078.002D270700C03-Jan-1403-Jan-1403-Jan-1400
270700.10078.003D270700C03-Jan-1403-Jan-1403-Jan-1400
270700.10078.003.D270700C03-Jan-1403-Jan-1403-Jan-1400
270700.10078.002.D270700C03-Jan-1403-Jan-1403-Jan-1400
351800.61664.001.A351800C03-Jan-1403-Jan-1403-Jan-1400
372100.11994.011372100C03-Jan-1403-Jan-1403-Jan-1400
356600.62259.013356600C03-Jan-1403-Jan-1403-Jan-1400
365900.11601.015.A365900D03-Jan-1403-Jan-1403-Jan-1400
365900.11067.010365900D03-Jan-1403-Jan-1403-Jan-1400
365900.11027.013.A365900D03-Jan-1403-Jan-1403-Jan-1400
365900.11601.016.A365900D03-Jan-1403-Jan-1403-Jan-1400
372100.11971.022372100D03-Jan-1403-Jan-1403-Jan-1400
372100.11994.012372100D03-Jan-1403-Jan-1403-Jan-1400
223900.62180.001223900D06-Jan-1406-Jan-1406-Jan-1400
372100.11984.036372100D03-Jan-1406-Jan-1406-Jan-1411
372100.11971.023372100D03-Jan-1406-Jan-1406-Jan-1411
372100.11853.033372100D03-Jan-1406-Jan-1406-Jan-1411

<colgroup><col><col><col><col span="3"><col><col></colgroup><tbody>
</tbody>

The table is 12000 rows and has data added daily but the array i used was 2:12002 etc. Th elead time and turnaround have formulas in them so they are blank until columns d e f and have dates entered etc. then it puts the lead time etc.
 
Upvote 0
What is the date interval for which the formula does not return what you expect? And, of course the value you want to see?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top