Formula to count unique with 3 criteria.

  • I have a spreadsheet containing data from our support ticketing system. I have a formula that has been killing me and am looking for any help I can get!


    I'm looking to count the total number of unique ticket numbers for each individual where the ticket had a activity of escalation. I also need to verify that the Call Driver is homes. Here's what I've tried.


    b c f k
    Activity Type Activity Logged By Ticket # Call Driver
    Escalation Steve Smith 2885656 Homes
    Escalation Steve Smith 2885656 Homes
    Inbound email Steve Smith 2885656 Homes
    Escalation Steve Smith 2890000 Homes
    Escalation Steve Smith 2870000 Streets




    =SUM(IF(FREQUENCY(IF(C:$C="Steve Smith",F:F)*(B:B="Escalation")*(k:k="Homes"),F:F)>0,1))


    The result should be 1 because there is only one unique ticket # by steve smith that was a escalation for homes.


    Thanks so much for any assistance!

  • Re: Formula to count unique with 3 criteria.


    Hello justcrazy,


    Welcome to Ozgrid.


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments

  • Re: Formula to count unique with 3 criteria.


    try this formula =SUMPRODUCT((B1:B5="Escalation")*(C1:C5="Steve Smith")*(K1:K5="Homes")*(IF(COUNTIF(F1:F5,F1:F5)=1,1,0))).....this is a array formula and needs to be conformed by "Ctrl+Shift+Enter"

  • Re: Formula to count unique with 3 criteria.


    Thanks for your reply. What I'm actually working out of is much larger than this. I've developed a 'working' function for my needs but I'm getting stuck on wildcards in a cell reference or if statement.


    =SUM(IF(FREQUENCY(IF(Workorders!$C$2:$C$25000=[COLOR="red"]Individuals!$B6[/COLOR],IF(Workorders!$B$2:$B$25000=[COLOR="red"]Individuals!$N$26[/COLOR],IF(Workorders!$K$2:$K$25000=[COLOR="red"]Individuals!O$27[/COLOR],IF(Workorders!$F$2:$F$25000<>"",MATCH("~"&Workorders!$F$2:$F$25000,Workorders!$F$2:$F$25000&"",0))))),ROW(Workorders!$F$2:$F$25000)-ROW(Workorders!$F$2)+1),1))


    What I need is for the cell references from Individuals! to incorporate wildcards.


    To simplify this I've test if statements with the below.
    A B C


    Steve Smith Steve Forumla


    =IF(A1="Steve"&"*",1,0) I get 0
    =IF(A1=B1&"*",1,0) I get 0


    Any idea how to get wildcards to work in a if statement and if so, how would that apply to my formula above?


    Here's a excel file to sample this.
    ozgrid.com/forum/core/index.php?attachment/37017/

    Files

    • Testfile.xlsx

      (18.04 kB, downloaded 59 times, last: )
  • Re: Formula to count unique with 3 criteria.


    Quote from pangolin;546438

    try this formula =SUMPRODUCT((B1:B5="Escalation")*(C1:C5="Steve Smith")*(K1:K5="Homes")*(IF(COUNTIF(F1:F5,F1:F5)=1,1,0))).....this is a array formula and needs to be conformed by "Ctrl+Shift+Enter"



    I ran this formula on my data but got 0 results. I suspect that I'm also finding issues with wildcards in this scenario also. I would need wildcards at the end of each reference. Trying this without cell references first. I'll post my results.

  • Re: Formula to count unique with 3 criteria.


    Using this formula, without cell references and * for wildcards I'm still getting 0 results when I run it against my data. It should definitely have results.


    =SUMPRODUCT((Workorders!B:B="Escalation")*(Workorders!C:C="Geni*")*(Workorders!K:K="Mainstreet*")*(IF(COUNTIF(Workorders!F:F,F:F)=1,1,0)))

  • Re: Formula to count unique with 3 criteria.


    justcrazy


    Please follow these guidelines when quoting:


    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant -- not the entire post.


    This will keep thread clutter to a minimum and make the discussion easier to follow.

  • Re: Formula to count unique with 3 criteria.


    Great news!


    Using a IF(LEFT I was able to modify my formula. for those who might find this later, I've performed far too long researching this so I'll post my findings and try to break down as much as I can.


    =SUM(IF(FREQUENCY(IF([COLOR="blue"]Workorders!$C$2:$C$25000[/COLOR]=[COLOR="lime"]Individuals!$B6[/COLOR]
    ,IF([COLOR="blue"]Workorders!$B$2:$B$25000[/COLOR]=[COLOR="blue"][COLOR="blue"][COLOR="lime"]Individuals!$N$26[/COLOR][/COLOR][/COLOR]
    ,IF(LEFT([COLOR="blue"]Workorders!$K$2:$K$25000[/COLOR],10)=[COLOR="lime"]Individuals!O$27[/COLOR]
    ,IF(Workorders!$F$2:$F$25000<>"",MATCH("~"&Workorders!$F$2:$F$25000,
    Workorders!$F$2:$F$25000&"",0))))),ROW(Workorders!$F$2:$F$25000)-ROW(Workorders!$F$2)+1),1))


    For each range, highlighted blue, are the cell ranges for my 3 criteria. The corresponding green cells are my references cells. So in my case, I'm looking in the 3 blue ranges for the 3 green cells.


    One of my cells needed to contain a wildcard, the bolded section. So if you have this need you can modify the 'IF's in the first part of the formula to use the method I have in bold.
    IF(LEFT(RANGE,Characters)=Cell reference
    In the range, I've selected the range to search through, characters is the number of characters that are in your cell reference. For example: Steve as a cell reference and 'Steve Smith' as a name I want returned from my range, I would put 5 in the characters.


    The botom part of the formula, underlined, I have no clue how to explain. What is relevant is that where you see ranges in my forumula, the range needs to be the range where you're looking for your unique values.


    I hope this saves someone some time!

  • Re: Formula to count unique with 3 criteria.


    As I found noted here, wildcards are not allowed in If statements. Not that I have found a workaround either, but it helps eliminate a source of frustration.


    EDIT : Note to self, always refresh screen prior to doling out helpful advice, things do get updated while working elsewhere.

    VBA Newb, but getting better everyday!

  • Re: Formula to count unique with 3 criteria.


    Just a small suggestion.....


    rather than "hardcoding" the character length [10] in this part


    LEFT(Workorders!$K$2:$K$25000,10)=Individuals!O$27


    use LEN function, i.e.


    LEFT(Workorders!$K$2:$K$25000,LEN(Individuals!O$27))=Individuals!O$27


    Now you can change cell O27 to anything you want without having to change the formula........