Countif on Conditions

[Vijay DSK]

Hi all,
This forum is really helpful for novices like me. Currently struck with an
issue which goes as...

i have 3 col like date, cust number, quote ref. In quote ref i have a
formula "(COUNTIF($D$416,D16)" to generate a number for the a customer,
what i am looking is this should be done on Date basis, in other words, i
should get a new quote number if the date changes

May be my example should give you clear idea,
Date (dd/mm/yyyy) Cust No. Quote Ref
01/10/2008 4628A 001
15/10/2008 4670B 001
16/10/2008 4628A 002

and so on,

Thanks in advance

 

[Max]

This presumes that there will be at most, only a single order per any cust
per date

Cust Nos are assumed running in D4 down
In E4: =IF(D4="","",TEXT(COUNTIF(D$44,D4),"000"))
Copy E4 down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik

 

[Vijay DSK]

Max,
Thanks for your advice, but my actual situation is there will be more
quotations on a single day per customer
Advise me the suitable

 

[Max]

.. my actual situation is there will be

more quotations on a single day per customer

Can you show how then should the quote refs be generated?
Take the sample data below for 2 customers, indicate in your reply:

Date_____ Cust QuoteR
1-Oct-08 4628A
1-Oct-08 4670B
1-Oct-08 4628A
15-Oct-08 4628A
15-Oct-08 4670B
15-Oct-08 4670B
16-Oct-08 4628A

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

 

[Vijay DSK]

Max,
My requirement is as follows, hope u can understand

Date_____ Cust QuoteR
1-Oct-08 4628A 001
1-Oct-08 4670B 001
1-Oct-08 4628A 001
15-Oct-08 4628A 002
15-Oct-08 4670B 002
15-Oct-08 4670B 002
16-Oct-08 4628A 003

Vijay DSK

 

[Max]

Dates are running in C4 down
In E4:
=IF(C4="","",IF(COUNTIF(C$4:C4,C4)>1,"",COUNTIF(C$4:C4,C4)))

In F4:
=TEXT(COUNT(E$4:E4),"000")
Select E4:F4, copy down. The results you seek will appear in F4 down.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

 

[Vijay DSK]

Max,
Tried your advise but some how i am confused... any how once again i am
giving my real time data as example

Col1 Col2 Col3 Col4 Col5
Col6
Sl No Date Cust Name Cust Code Cust Ref Quote Ref
1 10/09/2008 ABC 610000-RO Tele.conv 001
2 23/09/2008 ABC 611900 Tele.conv 001
3 26/09/2008 ABC 610902 E-mail 001
4 26/09/2008 ABC 611700 Tele.conv 001
5 26/09/2008 ABC 611700 Tele.conv 001
6 27/09/2008 ABC 611700 Tele.conv 002
7 28/09/2008 ABC 611700 Tele.conv 003
8 26/09/2008 ABC 611702 Tele.conv 001
9 27/09/2008 ABC 610409 Tele.conv 001
10 27/09/2008 ABC 610409 Tele.conv 001
11 01/10/2008 ABC 611501 Telecon 001
12 11/10/2008 ABC 610700 Tele.conv 001
13 22/10/2008 XYZ XYZ0004028 Tele.conv 001
14 23/10/2008 XYZ XYZ0004086 Er.Visit 001
15 23/10/2008 XYZ XYZ0004086 Er.Visit 001
16 23/10/2008 XYZ XYZ0004086 Er.Visit 001

Take the example rows from 4 to 7, row 4 and 5 were generated on 26/10/2008
so, it should have "001" for both rows and row 6 represents a change in date
for the same customer ( refer cust.code) so, it should have "002" and row 7
also represents a change in date for the same customer, so it should have
"003".

Like wise for every change in col2, col4 column 6 should be updated.

Hope i am clear in asking, excuse me if i committed any confusion.

 

[Max]

Nothing to get confused really. Just sort your entire table by the dates col,
then apply the formulas in 2 adjacent empty cols to the right. It should work
ok. Adjust the 1st formula to point at where the dates actually start (I had
presumed it was in C4 down earlier, as stated in the response). The 2nd
formula is to point at where the 1st formula starts.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik
---

 

[vezerid]

I assume your data start in row 4. You can use this formula in Row 4
for QuoteRef and copy.

=SUMPRODUCT(($A$4:A4=A4)*($D$44=D4))

HTH
Kostis Vezerides

 

[Vijay DSK]

Max,
Did exactly how u advised (changed the formula pointers also) the result is
as follows :
Col A Col B Col C Col D Col E Col F Col G
Sl No Date Cust Name Cust Code Cust Ref Quote Ref
1 10/09/2008 ABC 610000-RO Tele.conv 1 001
2 23/09/2008 ABC 611900 Tele.conv 1 002
3 26/09/2008 ABC 610902 E-mail 1 003
4 26/09/2008 ABC 611700 Tele.conv "" 003
5 26/09/2008 ABC 611700 Tele.conv "" 003
6 26/09/2008 ABC 611700 Tele.conv "" 003
7 26/09/2008 ABC 611702 Tele.conv 003
8 26/09/2008 ABC 611702 Tele.conv 003
9 27/09/2008 ABC 610409 Tele.conv 1 004
10 27/09/2008 ABC 610409 Tele.conv 004
11 01/10/2008 ABC 611501 Telecon 1 005
12 11/10/2008 ABC 610700 Tele.conv 1 006
13 22/10/2008 XYZ XYZ0004028 Tele.conv 1 007
14 23/10/2008 XYZ XYZ0004086 Er.Visit 1 008
15 23/10/2008 XYZ XYZ0004086 Er.Visit 008
16 23/10/2008 XYZ XYZ0004086 Er.Visit 008

Vezerid,
Take above example and after using your formula the result is as follows,
just go through the post what i am looking at please.
Col A Col B Col C Col D Col E Col F
Sl No Date Cust Name Cust Code Cust Ref Quote Ref
1 10/09/2008 ABC 610000-RO Tele.conv 1
2 23/09/2008 ABC 611900 Tele.conv 1
3 26/09/2008 ABC 610902 E-mail 1
4 26/09/2008 ABC 611700 Tele.conv 1
5 26/09/2008 ABC 611700 Tele.conv 2
6 26/09/2008 ABC 611700 Tele.conv 3
7 26/09/2008 ABC 611702 Tele.conv 1
8 26/09/2008 ABC 611702 Tele.conv 2
9 27/09/2008 ABC 610409 Tele.conv 1
10 27/09/2008 ABC 610409 Tele.conv 2
11 01/10/2008 ABC 611501 Telecon 1
12 11/10/2008 ABC 610700 Tele.conv 1
13 22/10/2008 XYZ XYZ0004028 Tele.conv 1
14 23/10/2008 XYZ XYZ0004086 Er.Visit 1
15 23/10/2008 XYZ XYZ0004086 Er.Visit 2
16 23/10/2008 XYZ XYZ0004086 Er.Visit 3

Request you both just copy & paste my data and advise me.