+ Reply to Thread
Results 1 to 15 of 15

Count unique values with IF formula?

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Count unique values with IF formula?

    I have two columns of data like this:
    Batch PO
    A 4500202477
    B 4500159684
    B 4500171273
    B 4500171273
    C 4500208603
    C 4500208603
    C 4500208603
    ...

    I want to add a third column that lists how many DIFFERENT PO#s are listed for each Batch No.
    For example, I'd like for this new column to show "1" for each row of batch A, "2" for B, and "1" for C (because all those PO#s are the same).

    Of course the numbers will repeat, I just need a quick way of seeing which batches had more than one PO assigned to them.
    I'd post some of my failed attempts at a function but they're too embarrassing!

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values with IF formula?

    In C1 and copy down.

    =SUMPRODUCT(($A$1:$A$7=A1)*($B$1:$B$7=B1))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count unique values with IF formula?

    [QUOTE=Fotis1991;3044404]In C1 and copy down.

    Thanks, but that only tells me how many times the batch is listed.

    I need to know how many DIFFERENT PO numbers are listed next to each batch.

  4. #4
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Count unique values with IF formula?

    you can also use the countifs function. If you have a large amount of data, I believe this formula will require less memory
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please click the * icon below if I have helped.

  5. #5
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Count unique values with IF formula?

    would this work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count unique values with IF formula?

    Thanks Melvinrobb, but these two formulas also only return the number of times a batch is listed.
    I think you were closer with the countifs formula, that's along the lines that I was guessing with before I gave up

  7. #7
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count unique values with IF formula?

    See if this workbook helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  8. #8
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count unique values with IF formula?

    That's brilliant! It's very helpful, unfortunately I have many many batches (and a much longer data set than just 7 rows).
    Plus, I simplified my batch codes. In reality they're crazy alphanumeric codes, so I foresee setting up this kind of formula being a real nightmare...

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values with IF formula?

    In C1 and copy down

    =IF(SUMPRODUCT(($A$1:$A1=A1)*($B$1:$B1=B1))=1,1,"")

    This will be a helper and hidden column.

    Then in D1 and copy down.

    =SUMPRODUCT(($A$1:$A$7=A1)*($C$1:$C$7=1))

  10. #10
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count unique values with IF formula?

    Thank you!! That's it!

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Count unique values with IF formula?

    You are welcome.

    Thanks for the reb.

  12. #12
    Registered User
    Join Date
    12-12-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Count unique values with IF formula?

    It's well-deserved!

  13. #13
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Count unique values with IF formula?

    I eventually got there! ha ha....
    using $C$1:$C$7=1 didn't dawn on me though. I kept trying to subtract the # of 1s from the total number, but that is much cleaner.

  14. #14
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Count unique values with IF formula?

    Quote Originally Posted by Sondra View Post
    That's brilliant! It's very helpful, unfortunately I have many many batches (and a much longer data set than just 7 rows).
    Plus, I simplified my batch codes. In reality they're crazy alphanumeric codes, so I foresee setting up this kind of formula being a real nightmare...
    This adapted method will handle as many batch codes as you need.
    Attached Files Attached Files

  15. #15
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Count unique values with IF formula?

    if your sample data (from post #1) are in the range A1:B7, put this formula in C1 and drag-fill down:

    Please Login or Register  to view this content.
    1009
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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