+ Reply to Thread
Results 1 to 12 of 12

Multiple count if with unique values,

  1. #1
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Multiple count if with unique values,

    Hi!
    Need help please!
    I want to create countifs formula to count how many unique PO's was issued for each buyer for 2012 with status Completed.

    Countifs works fine but my problem is nesting a formula to determine only unique values.

    Here below are sample table.

    BUYER PO DATE STATUS
    GOPA 692735 11/05/2012 Completed
    GOPA 692090 13/10/2012 Completed
    GOPA 706391 18/04/2012 Partially completed
    CRIZ 697459 19/04/2012 Partially completed
    CRIZ 697459 26/04/2012 Completed
    CRIZ 697459 04/05/2012 Completed
    CRIZ 697459 10/12/2012 Completed
    CRIZ 697459 23/01/2013 Completed
    CRIZ 697459 15/11/2012 Completed
    CRIZ 697459 16/10/2012 Not compl.
    CRIZ 697459 26/06/2013 Completed
    CRIZ 697459 06/12/2012 Completed
    CRIZ 696494 30/04/2013 Completed
    CRIZ 742422 04/12/2012 Completed
    GOPA 704663 24/04/2012 Completed
    GOPA 701281 24/04/2012 Completed
    GOPA 699192 07/09/2012 Completed
    CRIZ 700892 12/06/2012 Completed
    CRIZ 695038 26/04/2012 Completed
    CRIZ 695020 24/05/2012 Completed
    CRIZ 698305 04/11/2013 Completed
    CRIZ 698318 04/11/2013 Partially completed
    GOPA 698281 18/12/2012 Partially completed
    GOPA 730951 05/10/2012 Completed
    CRIZ 695524 09/10/2012 Partially completed

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple count if with unique values,

    Try this...

    With your data in the range A2:D26...

    F2 = GOPA
    F3 = CRIZ

    Enter this array formula** in G2 and copy down as needed:

    =SUM(IF(FREQUENCY(IF(A$2:A$26=F2,IF(D$2:D$26="Completed",B$2:B$26)),B$2:B$26),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple count if with unique values,

    Thanks! will denitely try this...

  4. #4
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple count if with unique values,

    How about the dates? could I just add another if function?

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple count if with unique values,

    Yes, but depending on how the date will be used, you may need 2 more IF statements.

    Can you be more specific?

  6. #6
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple count if with unique values,

    With the same earlier criteria, I would like to have the a count of all PO's completed for 2012 for each buyer.
    Thanks in advance.

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Multiple count if with unique values,

    Hi,

    adjusting Tony Valko's formula (I hope correctly)


    =SUM(IF(FREQUENCY(IF(A$2:A$26=F2,IF(D$2:D$26="Completed",IF(YEAR($C$2:$C$26)=2012,B$2:B$26))),B$2:B$26),1))

    Array formula to be confrmed with control+shift+enter

    Hope it helps
    Last edited by canapone; 11-21-2013 at 05:02 AM.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple count if with unique values,

    Yes, that's correct.

  9. #9
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple count if with unique values,

    Many Thanks to both of you CANAPONE and TONY.
    I guess there is one more thing, my PO column have some blank rows which should not be counted.
    Please let me know what variation in the formula can be done.

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple count if with unique values,

    Just add another IF statement:

    =SUM(IF(FREQUENCY(IF(A$2:A$26=F2,IF(B$2:B$26<>"",IF(D$2:D$26="Completed",IF(YEAR($C$2:$C$26)=2012,B$2:B$26)))),B$2:B$26),1))

    Still array entered.

  11. #11
    Registered User
    Join Date
    11-16-2013
    Location
    Angola
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Multiple count if with unique values,

    Thanks Tony! Totally works...

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple count if with unique values,

    You're welcome. Thanks for the feedback!

+ 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. Need a count of unique values in col. with multiple values in a cell.
    By DrewS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2013, 01:03 PM
  2. count unique values in multiple worksheets with multiple criteria
    By craig04 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2012, 03:26 PM
  3. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  4. Replies: 16
    Last Post: 01-11-2012, 10:51 AM
  5. Replies: 5
    Last Post: 04-21-2011, 05:22 PM

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