Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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$4:D16,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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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$4:D4,D4),"000")) Copy E4 down -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Vijay DSK" wrote: i have 3 col like date, cust number, quote ref. In quote ref i have a formula "(COUNTIF($D$4:D16,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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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" wrote: 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$4:D4,D4),"000")) Copy E4 down -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "Vijay DSK" wrote: i have 3 col like date, cust number, quote ref. In quote ref i have a formula "(COUNTIF($D$4:D16,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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
.. 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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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" wrote: .. 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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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 --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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$4:D4=D4)) HTH Kostis Vezerides On Oct 31, 8:42*am, Vijay DSK wrote: 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$4:D16,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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif on Conditions
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. "vezerid" wrote: 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$4:D4=D4)) HTH Kostis Vezerides On Oct 31, 8:42 am, Vijay DSK wrote: 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$4:D16,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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
Countif with 2 conditions | Excel Worksheet Functions | |||
Using countif with 2 conditions | Excel Worksheet Functions | |||
COUNTIF 2 conditions | Excel Worksheet Functions | |||
COUNTIF - 2 conditions | Excel Worksheet Functions |