• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to count entries based on multiple conditions

C

Chaitra

New Member
Hi All,


I am new to this forum and my first post.

I have a complicated task which i need to complete in excel.


I have many columns

A: Unique ID B:Install Time, C:Ticket 1 Creation Time, D:Ticket 2 creation time.E:Ticket 3 Creation time so on...

I need to get something like below

15 days after install time , how many tickets were created in that time frame for each of the unique ID.

Note: Time is in the format 2012-08-03T16:25:27

Something like below

' Tickets created

Time since installation <2 2-5 5-10 10-15 >15

15 days 3224

30 days 109

45 days 101

60 days 84

75 days 62

90 days 53 '


Any help at the earliest will be highly appreciated


Thanks so much


Also the sample worksheet can be downloaded from the below link


http://www.2shared.com/file/Kh3DYWpm/sample.html
 
Welcome Chaitra,


You can upload the sample workbooks from the below links.


http://www.speedyshare.com/

http://www.2shared.com/


Thanks,

Suresh Kumar S
 
Hi, Chaitra!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your second post...


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


So be patient and wait, you've started this topic only 1 hour ago.


Regards!
 
Hi Chaitra ,


Your sample worksheet has too little data ; can you explain a little further ?


1. Your data is in 8 columns , column A through column H , is this correct ?


2. The entries in column A are all unique ; there will be no duplicates.


3. The counts of tickets , in column C - will it be entered , or is it to be calculated ?


4. The date / time format is a non-standard one , and in order to calculate days since installation , the date / time values will have to be converted to a standard format ; this will require helper columns , if that is acceptable ; if not , we will have to use VBA ; is that acceptable ?


Narayan
 
Hi Chaitra,


I have change little bit in the Format.. But not able to check properly.. that I am providing correct answer or not, due to very less data, and huge list of condition, and your provided answer are also assumption.. and to check properly, I have to wait for a day.. as you want result according to TODAY... :)


Can you please check the attached..


https://dl.dropbox.com/u/78831150/Excel/count%20entries%20based%20on%20multiple%20conditions%28Chaitra%29.xlsx


The formula I have used is as follow..

[pre]
Code: 
=SUMPRODUCT(
(TODAY()-DATEVALUE(LEFT(Install_Time,10))>$A10)*
(TODAY()-DATEVALUE(LEFT(Install_Time,10))<=$A11)*
(Total_No_of_tickets_created>B$9)*
(Total_No_of_tickets_created<=C$9))
[/pre]
BTW.. I am also waiting for the response.. raised be Narayan..

Regards,

Deb
 
Thanks a lot Narayan and Deb for the posts.


1. Your data is in 8 columns , column A through column H , is this correct ?

-Yes, right


2. The entries in column A are all unique ; there will be no duplicates.

-Yes , right


3. The counts of tickets , in column C - will it be entered , or is it to be calculated?

-It will be entered .


4. The date / time format is a non-standard one , and in order to calculate days since installation , the date / time values will have to be converted to a standard format ; this will require helper columns , if that is acceptable ; if not , we will have to use VBA ; is that acceptable ?

--It is ok to use Helper columns.


See the attached revised sample file, where i have put it loads of data( Data Sheet)


http://www.2shared.com/file/JdW7bm3_/sample.html


Kindly guide me
 
Hi Chaitra ,


Can you check your file here ?


http://www.2shared.com/file/P5YDFSFh/sample-2.html


I have put in the formula only in one cell ; if you think it is OK , you can extend it to the other cells.


Narayan
 
Also seeing what Deb has given , wanna inform that i wanna map the ticket time with the install time, not only the number of tickets.


That is for ex:


Install time is 2012-10-31T21:08:08

2 tickets were created by that unique ID with time

2012-10-31T22:09:43 and 2012-11-07T13:15:52, then i want to count this entry as 1 in the range <=2 within 15 days since install..

<=2 2-5

15 days 1


This is what I need.
 
Hi Chaitra ,


I have not understood ; can you please explain ?


1. You have a list of unique IDs , along with the date of installation of each unique ID.


2. You also have a list of all the tickets which have been issued against each ID , giving the date + time of registration of each ticket.


3. You have manually counted the number of tickets issued against each ID , and entered this against each ID.


4. The output table has row headers giving the days since installation in slots such as 15 , 30 , 45 , 60 , 75 , 90 ,...


5. The output table has column headers giving the number of tickets issued , in slots such as "less than 2" , "between 2 and 5" , "between 5 and 10" , "between 10 and 15" , "above 15" ,...


6. I have put in a formula for cell B10 ; can you put in the correct value and explain how you got this value ?


Narayan
 
Hi Narayan,


Thanks for such quick response.


I have explained everything in the attached excel sheet.

Kindly take a look and let me know how do i do that.


http://www.2shared.com/file/x-LGhzqr/sample-3.html


Thanks a lot for all the help. Really appreciate it...
 
Hi Chaitra ,


Sorry , but it is still not clear to me.


I have transferred your data to a new sheet in your file , and uploaded it here :


http://www.2shared.com/file/Dp8wE-nN/sample-3.html


Can you go through the sheet and say , based on what is shown there , how your output table should be filled in ? I still cannot understand how you have got values of 1 in C14 , D15 , E15 and D16.


In case any of the calculations shown there is wrong , please explain what my mistake is.


Narayan
 
hi,


hope this is what you want, the yellow color is helping column for calculation.


http://speedy.sh/SfH9j/Copy-of-sample-3.xlsx


to download :

click on the link above then click on your file name.xls
 
Thanks Koi, it was really damn helpfull...I was able to do the calculation based on your formulas.


Kudos!!! Cant thank you enough!


Thanks Narayan!


Chaitra
 
You must log in or register to reply here.
Back
Top