+ Reply to Thread
Results 1 to 8 of 8

Ignoring duplicates in formula

  1. #1
    Registered User
    Join Date
    10-26-2010
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    27

    Ignoring duplicates in formula

    Hi everyone, I am trying to get a value based on different values.

    No Run Office QUALITY-CONTROL-EMPLOYEE
    No Run Office QUALITY-CONTROL-EMPLOYEE
    No Run Office QUALITY-CONTROL-EMPLOYEE
    Passed Office ANALYST
    No Run Office SECRETARY
    No Run Office SECRETARY
    No Run Office CEO
    Failed Office OFFICE LEAD
    Not Completed Office CEO

    I need a formula that will give me a total for the "No Run", a total for "Passed", a total for "Failed" and a total for "Not Completed. Now comes the challenging part. The formula needs to check if the criteria "Office" in the second column is met, if it is met it will have to give me the total for "No Run" for example but only for unique values in column 3. So it should only give me a total of 3 for "No Run" because "No Run" only applies to three unique names in column 3.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,774

    Re: Ignoring duplicates in formula

    Have you tried an If(and( ... stmt?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,041

    Re: Ignoring duplicates in formula

    Maybe use SUMPRODUCT with --, coercing to a count rather than a sum
    or maybe pivot table?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  4. #4
    Registered User
    Join Date
    10-26-2010
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Ignoring duplicates in formula

    Quote Originally Posted by Sambo kid View Post
    Have you tried an If(and( ... stmt?
    Yes, I've tried an IF(AND(... statement but that didn't help me eliminate the duplicates. I've also tried a two part approach by first using a COUNTIF statement. The COUNT IF looks like this: COUNTIFS(A:A,"No Run",C:C,C1) That will show me how many times "No Run" in column A appears for the value in column C. I then use a DCOUNTA statement but that doesn't give me the correct amount.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Ignoring duplicates in formula

    Try this. Assuming your data is in A1C9 (your sample data), in D1, copied down, use this...
    =IF(AND(B1="Office",COUNTIF($C$1:C1,C1)=1),A1,"")
    or
    =IF(COUNTIFS($B$1:B1,"Office",$C$1:C1,C1)=1,A1,"")

    You can then base whatever counts you want, on that column
    Last edited by FDibbins; 07-28-2013 at 11:50 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Ignoring duplicates in formula

    maybe this way
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  7. #7
    Registered User
    Join Date
    10-26-2010
    Location
    World
    MS-Off Ver
    Excel 2007
    Posts
    27

    Re: Ignoring duplicates in formula

    Quote Originally Posted by martindwilson View Post
    maybe this way
    Worked like a charm, thanks!

  8. #8
    Forum Contributor
    Join Date
    12-22-2010
    Location
    Rio, Brazil
    MS-Off Ver
    Excel 2010, 2016
    Posts
    209

    Re: Ignoring duplicates in formula

    assuming your data in A2:C10 maybe...

    =SUMPRODUCT(--(A2:A10="No Run"),--(B2:B10="Office"),--(MATCH(C2:C10,C2:C10,0)=ROW(C2:C10)-ROW(C2)+1))

    M.
    Marcelo Branco

+ 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] ignoring duplicates in countifs
    By banvir1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2013, 01:21 PM
  2. [SOLVED] formula that can count number of orders booked on a Date ignoring duplicates
    By Frankmed in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-20-2013, 05:26 PM
  3. Ignoring Duplicates when Calculating a Median
    By trfarley05 in forum Excel General
    Replies: 3
    Last Post: 06-08-2012, 09:42 AM
  4. Computing a cumulative while ignoring duplicates
    By Apriljustice in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-26-2010, 01:20 PM
  5. Ignoring Duplicates
    By Editz in forum Excel General
    Replies: 5
    Last Post: 01-19-2007, 07:24 AM

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