Unique count of values in a column satisfying multiple conditions in another column

  • Hi


    I require to count unique entries in excel albeit on satisfying two criterias. My data is organised like this:


    Col A Col B
    A Jun
    A July
    A Aug
    B Jun
    B Jul
    B Jul
    C Jun
    C Aug
    Now I want a unique count of entries in column A that fall BOTH in June AND July. So in the above case value A comes in June and July therefore the count for A should be 1. Similarly B also falls in Jun and July so the count should be 1 for B as well. However C comes in June but not in July and hence the count for C should be 0. So the final answer should be 2 achieved as (1+1+0). I would like to extend this formulae to include more criterias. Request someone to kindly help.

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    How are your dates entered? Attach an example workbook

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Hi Sir


    I do not have dates entered but I have months written the same way as I shown in my example.My data entered in excelk is in the same fashion.Hope that helps.

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    You haven't said which version of Excel you are using, that's partly why I asked for an example workbook, plus I haven't time to recreate a workbook.

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Hi Murtuza


    Either one of these formulas in C2 of the example workbook


    =--IF(B2="Jun",COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Jul")>0,0)
    =--IF(B2="Jun",SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="Jul"))>0,0)


    Format C2 as 0;-0;;@


    And then fill down


    Or do you just want to show a total in one cell?

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Can also be done like this


    =(B2="Jun")*(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,"Jul")>0)
    =(B2="Jun")*(SUMPRODUCT(($A$2:$A$10=A2)*($B$2:$B$10="Jul"))>0)

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    VBA alternative..


    Hey.. maybe i like typing.. ;)


  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Hi
    I would appreciate if I could get a formula to throw up the total count in one cell.

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Hi Thanks for your reply.Can you please tell me how to execute this macro I know how to run udf's but not macros.

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    Ooops..


    Change this line:

    Code
    Set Srch = .AutoFilter.Range.Offset(1, 1).Resize(.AutoFilter.Range.SpecialCells(12).Count + 1, 1).SpecialCells(12)


    to:

    Code
    Set Srch = .AutoFilter.Range.Offset(, 1).Resize(.AutoFilter.Range.SpecialCells(12).Count + 1, 1).SpecialCells(12)


    Check out the attachment in my Previous post to see where to put the code..


    If you can't.. just add an ActiveX CommandButton to your Sheet and add the code to it..

  • Re: Unique count of values in a column satisfying multiple conditions in another colu


    UDF


    Use in cell like


    =UNIQIF(A2:B10,"Jun","Jul")
    You can add more condition(s) like
    =UNIQIF(A2:B10,"Jun","Jul","Aug","Sep","OK")


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!