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

Murtuza · Dec 29th 2013

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.

**royUK** · Dec 29th 2013

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

How are your dates entered? Attach an example workbook

Murtuza · Dec 29th 2013

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.

**royUK** · Dec 29th 2013

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.

Murtuza · Dec 29th 2013

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

Hi Sir

Please find the workbook attached. I am using excel 2010.

holycow · Dec 29th 2013

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?

holycow · Dec 29th 2013

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)

apo · Dec 29th 2013

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

VBA alternative..

Hey.. maybe i like typing..

Code

Private Sub CommandButton1_Click()
    Dim Srch As Range, v As Range, z, i As Long, flg As Boolean
    Dim Val1, Val2
    With Sheets("Sheet1")
        With CreateObject("scripting.dictionary")
            For Each it In Sheets("Sheet1").Columns(1).SpecialCells(2).Offset(1)
                x0 = .Item(it.Value)
            Next
            z = .keys
        End With
        For i = LBound(z) To UBound(z) - 1
            .Range("A1:B" & Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, z(i)
            Set Srch = .AutoFilter.Range.Offset(1, 1).Resize(.AutoFilter.Range.SpecialCells(12).Count + 1, 1).SpecialCells(12)
            Set Val1 = Srch.Find("Jun", LookIn:=xlValues)
            Set Val2 = Srch.Find("Jul", LookIn:=xlValues)
            If Not Val1 Is Nothing And Not Val2 Is Nothing Then flg = True
            Set v = Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(12).Cells(1)
            If flg = True Then
                v = "1"
            Else
                v = "0"
            End If
            flg = False
        Next i
        If .AutoFilterMode Then .AutoFilterMode = False
        With Sheets("Sheet1").Range("C" & .Range("A" & Rows.Count).End(xlUp).Row + 1)
           .Formula = "=Sum(C2:C" & Range("C" & Rows.Count).End(xlUp).Row & ")"
           .Offset(, -1).Value = "Total"
           .Offset(, -1).Resize(1, 2).Font.Bold = True
        End With
    End With
End Sub

Display More

Murtuza · Dec 29th 2013

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.

Murtuza · Dec 29th 2013

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.

apo · Dec 29th 2013

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..

jindon · Dec 29th 2013

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")

Code

Function UNIQIF(ByVal rng As Range, ParamArray x()) As Long
    Dim a, e, s, i As Long
    a = rng.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To UBound(a, 1)
            If a(i, 1) <> "" Then
                If Not .exists(a(i, 1)) Then
                    Set .Item(a(i, 1)) = _
                    CreateObject("Scripting.Dictionary")
                    .Item(a(i, 1)).CompareMode = 1
                    For Each e In x
                        .Item(a(i, 1))(e) = False
                    Next
                End If
                If .Item(a(i, 1)).exists(a(i, 2)) Then
                    .Item(a(i, 1))(a(i, 2)) = True
                End If
            End If
        Next
        For Each e In .keys
            For Each s In .Item(e).keys
                If Not .Item(e)(s) Then .Remove e
            Next
        Next
        UNIQIF = .Count
    End With
End Function

Display More

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

Participate now!

Share