+ Reply to Thread
Results 1 to 5 of 5

Remove duplicates, then sumif, with only formula

  1. #1
    Registered User
    Join Date
    08-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    18

    Remove duplicates, then sumif, with only formula

    I have the following sheet:

    A, X, 7
    B, Y, 10
    C, Z, 8
    C, Z, 8
    B, Y, 10
    B, W, 20
    C, Y, 10

    I need a formula that will SUM column 3 IF column 1 = B,
    That does not sum if a duplicate occurs in column 2

    So when entering B formula returns = 30 (first B row + third B row)(ignores second B-Y row)
    When C is entered, returns = 18 (3rd column sum of C's ignoring duplicates in second column)

    FYI: If 2nd column are =, then 3rd column are equal in my sheet.

    I basically want to remove duplicates and then do a sumif, but I want it to do it all in a formula without having to do the remove duplicates manually or have to run a macro. Is it possible?

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

    Re: Remove duplicates, then sumif, with only formula

    Hmm first thoughts
    Attached Files Attached Files
    Last edited by martindwilson; 07-13-2011 at 07:17 AM.
    "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

  3. #3
    Registered User
    Join Date
    08-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Remove duplicates, then sumif, with only formula

    On second thought... I think it might be easier to solve this a different way.

    Is there a way to copy Column B to Column C if column A hasn't yet been duplicated?

    EX: (Column C has formula in it)("-" means blank cell)

    A, B, C
    X, 1, 1
    Y, 2, 2
    X, 1, -
    Z, 3, 3
    Y, 2, -
    Z, 3,-

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

    Re: Remove duplicates, then sumif, with only formula

    for that example in c1
    =IF(COUNTIF($A$1:A1,A1)=1,B1,"")

  5. #5
    Registered User
    Join Date
    08-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Remove duplicates, then sumif, with only formula

    Quote Originally Posted by martindwilson View Post
    for that example in c1
    =IF(COUNTIF($A$1:A1,A1)=1,B1,"")
    Thanks, that is much easier to use than that first idea I had. Thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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