Thanks:  0
Likes:  0

Thread: Fomrula for unique records between 2 dates

1. Fomrula for unique records between 2 dates

Hi, I have a spreadsheet with the following type of information but is 2000 rows:

 Customer Date Lead Time A 01/01/2014 1 A 04/01/2014 3 B 05/01/2014 1 C 04/02/2014 3 A 05/02/2014 5

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.

2. Re: Fomrula for unique records between 2 dates

E1: 1-Jan-2014

F1: 31-Jan-2014

G1, control+shift+enter, not just enter:
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:
Code:
```=IFERROR(1/(1/MIN(IF(\$B\$2:\$B\$6>=\$E1,
IF(\$B\$2:\$B\$6<=\$F1,\$C\$2:\$C\$6)))),"")
```

3. Re: Fomrula for unique records between 2 dates

E1: 1-Jan-2014

F1: 31-Jan-2014

G1, control+shift+enter, not just enter:
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:
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?

4. Re: Fomrula for unique records between 2 dates

Originally Posted by JimetyBob
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...
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?

5. Re: Fomrula for unique records between 2 dates

Im still getting the formula returning a blank?? All you help is greatly appreciated..

6. Re: Fomrula for unique records between 2 dates

Originally Posted by JimetyBob
Im still getting the formula returning a blank?? All you help is greatly appreciated..
Did you confirm the formula with control+shift+enter?

7. Re: Fomrula for unique records between 2 dates

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.

8. Re: Fomrula for unique records between 2 dates

Originally Posted by JimetyBob
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.
Would you post a relevant small sample along with the lead time you want to see?

9. Re: Fomrula for unique records between 2 dates

Would you post a relevant small sample along with the lead time you want to see?
 Avante Item Code Account Number Customer Date In Due Date Date Out Lead Time (Working Days) Turnaround Time (Working Days) 270700.10078.032 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.61721.002 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.61721.004 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.62256.007 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.62256.002 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.62256.006 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 270700.62256.008 270700 A 02-Jan-14 02-Jan-14 02-Jan-14 0 0 372100.11994.011 372100 B 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.61806.005 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.61806.006 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.61806.007 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.62061.003 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.62061.004 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.62061.005 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 320700.60156.003A 320700 C 02-Jan-14 02-Jan-14 02-Jan-14 0 0 371000.61015.005 371000 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 371000.61016.007 371000 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 270700.10078.002D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 270700.10078.003D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 270700.10078.003.D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 270700.10078.002.D 270700 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 351800.61664.001.A 351800 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 372100.11994.011 372100 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 356600.62259.013 356600 C 03-Jan-14 03-Jan-14 03-Jan-14 0 0 365900.11601.015.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 365900.11067.010 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 365900.11027.013.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 365900.11601.016.A 365900 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 372100.11971.022 372100 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 372100.11994.012 372100 D 03-Jan-14 03-Jan-14 03-Jan-14 0 0 223900.62180.001 223900 D 06-Jan-14 06-Jan-14 06-Jan-14 0 0 372100.11984.036 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1 372100.11971.023 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1 372100.11853.033 372100 D 03-Jan-14 06-Jan-14 06-Jan-14 1 1

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.

10. Re: Fomrula for unique records between 2 dates

What is the date interval for which the formula does not return what you expect? And, of course the value you want to see?

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•