Fomrula for unique records between 2 dates

[JimetyBob]

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

<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.

 

[JimetyBob]

The date interval is between and including 1/1/14 to 31/1/14 and the value for min i would expect to see is 0. And if I did the same formula but MAX would be 1. Thanks

 

[Aladin Akyurek]

The date interval is between and including 1/1/14 to 31/1/14 and the value for min i would expect to see is 0. And if I did the same formula but MAX would be 1. Thanks

I think you want the formula not to eliminate empty cells from consideration. If so:

I1: 01-Jan-2014

J1: 31-Jan-2014

K1, control+shift+enter:

=MIN(IF($E$2:$E$35>=$I1,IF($E$2:$E$35<=$J1,$G$2:$G$35)))

Note that the formula looks at Due Date.

For the max value, replace MIN with MAX.

Aternatively, just enter:

=AGGREGATE(15,6,$G$2:$G$35/(($E$2:$E$35>=$I1)*($E$2:$E$35<=$J1)),1)

=AGGREGATE(14,6,$G$2:$G$35/(($E$2:$E$35>=$I1)*($E$2:$E$35<=$J1)),1)

 

[Matty]

E1: 1-Jan-2014

F1: 31-Jan-2014

G1, control+shift+enter, not just enter:

=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:

=IFERROR(1/(1/MIN(IF($B$2:$B$6>=$E1,
  IF($B$2:$B$6<=$F1,$C$2:$C$6)))),"")

Why the division in your second formula, Aladin?

Thanks,

Matty

 

[Aladin Akyurek]

Why the division in your second formula, Aladin?

Thanks,

Matty

1) If E1 does not occur, we get "" instead of 0.
2) If cells corresponding to E1 text /blank (not numbers), we get "" instead of 0.

This coverage is OK.

3) However, the formula will return a "", instead of 0, while there is a real 0 (min value) corresponding to E1.

The latter would be admissible with dates.

Note. I understand from JimetyBob that here empty cells are OK with 0 as min value in consequence.

 

[JimetyBob]

I think you want the formula not to eliminate empty cells from consideration. If so:

I1: 01-Jan-2014

J1: 31-Jan-2014

K1, control+shift+enter:

=MIN(IF($E$2:$E$35>=$I1,IF($E$2:$E$35<=$J1,$G$2:$G$35)))

Note that the formula looks at Due Date.

For the max value, replace MIN with MAX.

This works perfectly, thank you very much for all your help!

 

[Aladin Akyurek]

I think you want the formula not to eliminate empty cells from consideration. If so:

I1: 01-Jan-2014

J1: 31-Jan-2014

K1, control+shift+enter:

=MIN(IF($E$2:$E$35>=$I1,IF($E$2:$E$35<=$J1,$G$2:$G$35)))

Note that the formula looks at Due Date.

For the max value, replace MIN with MAX.

This works perfectly, thank you very much for all your help!

You are welcome.