+ Reply to Thread
Results 1 to 14 of 14

Need Sum based on Duplicate IDs

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Question Need Sum based on Duplicate IDs

    Hi Guys,

    I am stuck with this situation, need help solving it.
    Please see the Data tab for details in the attached file.

    I was able to use a pivot table to get the required results but I am not able to do it with formula, the problem is due to the blank cells in the ID (C column).
    Same/Duplicate IDs have two different completion status
    1. Completed
    2. Agent missed

    I need the sum of calls completed by agents where the same was missed and there are multiple criteria I need to follow.
    Year, Week, Agent Name, etc.
    Sum Required.JPG

    * Sum or Count is based on ID (C) as a unique identifier
    If ID (C) and Agent Name (D) are blank then Status is "In Que"
    If ID (C) is blank but Agent Name (D) is not blank then Status will be based on the value of F, G, and H
    If Missed (F), Accepted (G), and Declined (H) are 0, then the status is "In Que".
    If Missed (F) is 1 but Accepted (G) and Declined (H) is 0, then the status is "Missed".
    If Missed (F) and Accepted (G) is 1 but Declined (H) is 0, then the status is "Completed".


    **I only need the sum of completed calls that were missed by an agent.
    Attached Files Attached Files
    Last edited by SyedTabassumAli; 03-21-2022 at 06:41 AM. Reason: Additional Information

  2. #2
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    If it is not clear.
    I need to get the number of missed calls where under the same ID the status is Agent Missed and no Status of Completed by the Agent.
    The problem I am having is the ID is the same but the Status is on different rows and both Agent missed and Completed have different agent names.

    Can someone help me solve this problem?

  3. #3
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    I am extremely sorry for my incompetence in not reading the guidelines properly.

    I have updated the file with fewer data and I hope this will help to confirm the results.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Sum based on Duplicate IDs

    You have PMd me twice asking for help. I asked you:

    a) to provide 10-20 rows data, not 16000. Well, we now have 60... which is still well in excess of 10-20.

    b) to provide manually calucalted expected results. There are none.

    It is unfair of you to expect us (unpaid individuals who do this to help others and to improve our own excel skills) to do EVERYTHING for you. If you were my Boss, I would have no choice but to work it out for myself.

    I, however, have the freedom to say NO. I am saying NO until YOU make it clear what is wanted. I do NOT play guessing games.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    Glenn really sorry,
    I must be frustrated for not getting it done by myself, I have tried doing it with different approaches using sumifs, vlookup, index, match, sumproduct, and filter but still not able to do it.

    I hope I'll be clear this time.
    In the Data sheet,
    1. If C is blank and F, G, H are 0 then the status should be "In Que"
    2. If C is blank and either F, G H is 1 then status should be as per title, "Missed", Accepted/Completed", "Declined".
    3. If C is not blank and same ID is getting repeated on the rows then we need to check if both F, G are 1,
    if only F is 1 then status will be "Missed",
    if only G is 1 then status will be "Completed",
    if both F and G are 1 then For G the status will be "Missed by other agent" and For F the status will be "Answered by other agent"
    4. Then I need to get the sum G as per status "Answered by other agent" to identify out of total missed calls how many were answered.
    5. The criteria will be A, B and D in Data sheet to get the final results as mentioned in the Required sheet.
    6. if H is 1 then status will be "Declined" even if under the same ID it was completed by other agent.

    I have updated the file again with only 20 rows of data in it.
    Thanks for your time looking into this.
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Sum based on Duplicate IDs

    Your criteria are incorrect. Review the bits in RED and recheck ALL criteria.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    Yes, you are right there is a typo mistake in the data at Row 15 and 16.

    If C is same/duplicate on multiple rows then status of F, if 1 will be "Answered by other Agent" and status of G, if 1 will be "Missed by other agent"

    Correction.JPG

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Need Sum based on Duplicate IDs

    The bit in red says

    If BOTH F & G are 1 then the ststus will be... whatever. In the 5 cases I have highlighted either F or G but NOT BOTH is 1.

    You have not done as i asked again. CHECK all criteria. Post a sheet that has 100% ACCURATE expected results (ANOTHER example: your critieri do not cover c = non blank and columns F, G & H total to zero. From your results, it is clear that it is probably "In Queue", but it does not say that anywhere.

  9. #9
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    That is the actual problem I am not able to solve, cause the 1 in F and G are not on the same row.
    In the image, the yellow highlighted is one block of ID where F and G are 1 but on different rows and both 1 need to have status of their own.

    For example, where ID is 1608130 on Row 14 to Row 17
    If F, G and H are all 0 then the status is "In Que"
    If F is 1 and G, H are 0 then the status is "Missed"
    If G is 1 and F, H are 0 then the staus is "Completed"
    but if F and G both are 1 even on separate rows then the status of 1 in F will be "Answered by other agent" and status of 1 in G will be "Missed by other agent"

    I am not able to set the status as per block of ID, as the call is completed by 2nd agent after it was missed by 1st agent, so in actuality the call was not missed as it was answered by second agent instead of the first agent.

    This should be the results according to status.
    Correction.JPG

    Quote Originally Posted by SyedTabassumAli View Post
    Glenn really sorry,
    I must be frustrated for not getting it done by myself, I have tried doing it with different approaches using sumifs, vlookup, index, match, sumproduct, and filter but still not able to do it.

    I hope I'll be clear this time.
    In the Data sheet,
    1. If C is blank and F, G, H are 0 then the status should be "In Que"
    2. If C is blank and either F, G H is 1 then status should be as per title, "Missed", Accepted/Completed", "Declined".
    3. If C is not blank and same ID is getting repeated on the rows then we need to check if both F, G are 1,
    if only F is 1 then status will be "Missed",
    if only G is 1 then status will be "Completed",
    if both F and G are 1 then For G the status will be "Missed by other agent" and For F the status will be "Answered by other agent"
    4. Then I need to get the sum G as per status "Answered by other agent" to identify out of total missed calls how many were answered.
    5. The criteria will be A, B and D in Data sheet to get the final results as mentioned in the Required sheet.
    6. if H is 1 then status will be "Declined" even if under the same ID it was completed by other agent.

    I have updated the file again with only 20 rows of data in it.
    Thanks for your time looking into this.
    Attached Files Attached Files
    Last edited by SyedTabassumAli; 03-23-2022 at 02:16 PM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Need Sum based on Duplicate IDs

    This proposal employs a helper column (N) on the Data sheet which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =AND(C2<>"",C2=C3,E2="Agent missed",E3="Completed")
    Column C on the Required sheet is populated using: =COUNTIFS(Data!D$2:D$20,A5,Data!N$2:N$20,TRUE)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    Thanks JeteMc,

    Yes, this solved the issue in a way if the data is the same as provided in the sample file.

    The thing is I have more than 11k rows and they are going to increase in the future, also the position of "Agent Missed" and "Completed" is not fixed, there can be rows where "Agent missed" can be under "Completed" then the (N) will not be TRUE.
    Also, is it possible to use an array formula as there are more than 11k rows I have in the Data sheet/tab.

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Need Sum based on Duplicate IDs

    Please try at

    require Row 5
    =SUM(IF((Data!$D$2:$D$20=A5)*Data!$F$2:$F$20,SUMIFS(Data!$G$2:$G$20,Data!$A$2:$A$20,$B$1,Data!$B$2:$B$20,$B$2,Data!$C$2:$C$20,Data!$C$2:$C$20)))

    but for 11k row this array formula will be very slow

    Better use Helper at Data N2
    =IF(F2,SUMIFS($G$2:$G$20,$C$2:$C$20,C2),)

    Then require Row 5

    =SUMIFS(Data!$N$2:$N$20,Data!$A$2:$A$20,$B$1,Data!$B$2:$B$20,$B$2,Data!$D$2:$D$20,$A5)
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    08-28-2013
    Location
    Pakistan
    MS-Off Ver
    Excel 2019
    Posts
    41

    Re: Need Sum based on Duplicate IDs

    Thanks Bo_Ry,

    Both are doing the job required. Excellent.
    I'll go with your suggestion using the helper N column.
    Is it possible if you can explain this =IF(F2,SUMIFS($G$2:$G$20,$C$2:$C$20,C2),)
    I never thought of doing it this way as I was focused on getting the status of each row according to the values in F, G, and H to get the total as per status.

    Thanks again, I'll mark the post as Solved as soon I get the explanation to understand what I was doing wrong.


    Quote Originally Posted by Bo_Ry View Post
    Please try at

    require Row 5
    =SUM(IF((Data!$D$2:$D$20=A5)*Data!$F$2:$F$20,SUMIFS(Data!$G$2:$G$20,Data!$A$2:$A$20,$B$1,Data!$B$2:$B$20,$B$2,Data!$C$2:$C$20,Data!$C$2:$C$20)))

    but for 11k row this array formula will be very slow

    Better use Helper at Data N2
    =IF(F2,SUMIFS($G$2:$G$20,$C$2:$C$20,C2),)

    Then require Row 5

    =SUMIFS(Data!$N$2:$N$20,Data!$A$2:$A$20,$B$1,Data!$B$2:$B$20,$B$2,Data!$D$2:$D$20,$A5)

  14. #14
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Need Sum based on Duplicate IDs

    The formula is very straightforward

    =IF(F2,SUMIFS($G$2:$G$20,$C$2:$C$20,C2),)

    If (Missed call ,then Sumifs Accepted call with the same ID, else Zero)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Removing duplicate rows based on single column duplicate values
    By Miasav90 in forum Excel General
    Replies: 7
    Last Post: 09-23-2021, 08:40 AM
  2. [SOLVED] VBA code to duplicate work sheet based on cell value and rename based on cell value
    By NASANASA in forum Excel Programming / VBA / Macros
    Replies: 51
    Last Post: 03-12-2020, 07:54 AM
  3. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2016, 04:36 PM
  4. Delete Duplicate rows with duplicate in 1 column based on value from another column
    By CraigR&M in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-30-2016, 02:06 PM
  5. [SOLVED] Duplicate Row based on a value in that row.
    By mrmulti in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-01-2014, 05:05 PM
  6. [SOLVED] Sort row based on duplicate value
    By nobodyNZ in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2014, 12:33 PM
  7. [SOLVED] Delete rows based on duplicate cell, but leaving first and last duplicate.
    By LadyNicole in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2013, 05:07 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1