Does anyone know a formula on how to count duplicate values as 1 for a given date?
eg:
5/5/2014 First Break
5/5/2014 First Break
5/5/2014 First Break
5/5/2014 First Break
5/5/2014 Second Break
5/5/2014 Second Break
5/5/2014 Second Break
5/5/2014 Second Break
I would like the formula to say
5/5/2014 Second Break=1
5/5/2014 First Break = 1
I am usingt forumla
But i need it to work by the dates to.
Please Login or Register to view this content.
Anyone?
Assuming in E1 is your date and In F1 is First break
in G1 try below
=SUMPRODUCT(COUNTIFS(E1,$A$1:$A$8,F1,$B$1:$B$8)/COUNTIFS($A$1:$A$8,E1,$B$1:$B$8,F1))
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
I gives me a value everytime there is an accurence.... I wish that the formula can only give a value (1) on the 1st occurence. Any Ideas? I've attached a copy of the sheet to show how the data is recorded from a phone system.
can you please specify what is the criteria for the first occurrence.
The criteria i want to use for the 1st occurence is the Agent State and the Punch Time....if they can somehow link together it will be great. I tried this Formula alsobut it breaks all the status into fractions until it returns one....but the problem is....it does the entire status...if i could've get it to that for specific dates....it would've been nice.Please Login or Register to view this content.
try below
=IF(COUNTIFS($E$2:E2,E2,$F$2:F2,F2)>1,"",SUMPRODUCT(COUNTIFS(L2,$L$2:$L$1500,F2,$F$2:$F$1500)/COUNTIFS($L$2:$L$1500,L2,$F$2:$F$1500,F2)))
I tried the formula and it worked great however the only problem is...because the time is one of the criteria i am using...some of the statuses have the same time for different employees. for example an error occured for everyone and I only have one status describing the logout reason. what the formula does it will read that logout status as one because everyone logged out at the sametime...any ideas lol? i'm stuck lol. there is no other unique criteria i can use.
Can you post a SMALL sample file and show us what results you expect?
A SMALL file will have about 20 rows worth of data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I've attached the sheet so you can see every status. The formula I used is also in there. a comment bvox also explaining the issue.
Sorry, your file is too big for me.
sorry I've attached a smaller file with the same info.
I'm not understanding this.
The in-file description mentions "status" but I don't see any references to a status. No column headers or fields are labeled as "status".
It sounds like you want to count unique entries that meet some condition. What range contains the entries to be counted and what is the condition?
The column that says agent state has the statuses. i want the status for each person count as one for example one agent has 6 First Break status for today. I want that First Break status to count as one for that specific datew. I've use the column that says time stamp as a criteria so if the agent mistakenly select First Break status again later in the day it will count it as one again. When add up the first break status for that agent for that specific date the total should be 2 and not 12.
NB: the statuses duplicates because of the Prod type column...its different log in queues.
Have you tried a pivot table
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Yep lol I've tried the pivot table but no luck. I tried this formula i madeBut the problem is that if John took lunch at 3:15:00 pm through 6 queues and tom took lunch at 3:15:00 pm through 6 queues the formula will only read John's lunch as 1 and not Tom because time is being used as the criteria.Please Login or Register to view this content.
In the sample file the Agent State column contains all the same entry, UnStaff. From F2:F81 every cell contains UnStaff.Originally Posted by Shellybelly
Sorry, but I'm passing on this one. Good luck!
in a nut shell i want all the duplicate status count as 1
I think you have an inconsistency in your use of arguments in the COUNTIFS. The correct order is
criteria range 1, criteria 1, criteria range 2, criteria 2.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks