CODEWITHSUNDEEP
excel
devphil
devphil

Reputation: 215

Excel distinct count with many conditions

  A            B       C   D  E       F           G           H   I  
115492          2009    10  42  5   90160624    15-Oct-09       WH  5
115492          2009    10  42  5   90160624    15-Oct-09       WH  5
115492          2009    10  42  5   90160624    15-Oct-09       WH  5
115492          2009    10  42  5   90160624    15-Oct-09       WH  5
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115481          2009    10  44  6   90170587    30-Oct-09       WH  3
115520          2009    11  45  5   90174693    5-Nov-09        WH  3
115520          2009    11  45  5   90174693    5-Nov-09        WH  3
115520          2009    11  45  5   90174693    5-Nov-09        WH  3
115520          2009    11  45  5   90174693    5-Nov-09        WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
115502          2009    11  46  6   90179821    13-Nov-09       WH  3
123057          2009    11  46  3   90182107    17-Nov-09       WH  3
123057          2009    11  46  3   90182107    17-Nov-09       WH  3
123057          2009    11  46  3   90182107    17-Nov-09       WH  3
123057          2009    11  46  3   90182107    17-Nov-09       WH  3
123056          2009    11  47  3   90186948    24-Nov-09       WH  3
123056          2009    11  47  3   90186948    24-Nov-09       WH  3
123056          2009    11  47  3   90186948    24-Nov-09       WH  3
123056          2009    11  47  3   90186948    24-Nov-09       WH  3
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115506          2009    11  47  3   90186673    24-Nov-09       WH  5
115496          2009    11  47  4   90187409    25-Nov-09       WH  3
115496          2009    11  47  4   90187409    25-Nov-09       WH  3
115496          2009    11  47  4   90187409    25-Nov-09       WH  3
115496          2009    11  47  4   90187409    25-Nov-09       WH  3
123058          2009    12  50  5   90198449    10-Dec-09       WH  3
123058          2009    12  50  5   90198449    10-Dec-09       WH  3
123058          2009    12  50  5   90198449    10-Dec-09       WH  3
123058          2009    12  50  5   90198449    10-Dec-09       WH  3
115522          2009    12  50  7   90200094    12-Dec-09       WH  7
115522          2009    12  50  7   90200094    12-Dec-09       WH  7
115522          2009    12  50  7   90200094    12-Dec-09       WH  7
115522          2009    12  50  7   90200094    12-Dec-09       WH  7
115522          2009    12  50  7   90200094    12-Dec-09       WH  7
123059          2009    12  51  6   90203897    18-Dec-09       WH  4
123059          2009    12  51  6   90203897    18-Dec-09       WH  4
123059          2009    12  51  6   90203897    18-Dec-09       WH  4
123059          2009    12  51  6   90203897    18-Dec-09       WH  4
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115539          2009    12  51  7   90204074    19-Dec-09       WH  5
115541          2009    12  51  7   90204389    19-Dec-09       WH  4
115541          2009    12  51  7   90204389    19-Dec-09       WH  4
115541          2009    12  51  7   90204389    19-Dec-09       WH  4
115541          2009    12  51  7   90204389    19-Dec-09       WH  4
115534          2009    12  52  2   90209074    28-Dec-09       WH  5
115534          2009    12  52  2   90209074    28-Dec-09       WH  5
115534          2009    12  52  2   90209074    28-Dec-09       WH  5
115534          2009    12  52  2   90209074    28-Dec-09       WH  5
115534          2009    12  52  2   90209074    28-Dec-09       WH  5
115534          2009    12  52  2   90209074    28-Dec-09       WH  5

I need to count the number of unique entries in column 'A' where column 'I' equals 3: I achieved this by following: 

=SUM(IF(FREQUENCY(A2:A76,A2:A76)>0,(I2:I76=3)*1,0))

What if I need to add more conditions like "where column 'I' equals 3 and column 'C' equals 11"?

I tried this: 

=SUM(IF(FREQUENCY(A2:A76,A2:A76)>0,(AND(I2:I76=3, C2:C76 = 11))*1,0))

But, this formula doesn't give me the correct number. what would be the right formula?

Upvotes: 2

Views: 306

Answers (3)

Gaijinhunter
Gaijinhunter

Reputation: 14685

I know you haven't asked for VBA and there are ways to solve this without it, but I really feel that VBA excels in this case since the code is so easy to manage and you can easily add/delete conditions for counting.

Sub SpecialCount()

Dim vArray As Variant
Dim lastRow As Long
Dim i As Long
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")

lastRow = range("A" & Rows.count).End(xlUp).Row
vArray = range("A1", "I" & lastRow).Value

On Error Resume Next
For i = 1 To UBound(vArray, 1)
    'Condition 1
    If vArray(i, 9) = 3 Then
        'Condition 2
        If vArray(i, 3) = 11 Then
            dict.Add vArray(i, 1), 1
        End If
    End If
Next

MsgBox dict.count & " unique entries found."
End Sub

How it works: First it finds the last cell in columnn A. I then dump the entire range of cells into a variant array since they are really fast to work with. Then it just loops through each colmumn A value and looks at the value in the 9th row (I) and 3rd row (C), and if they match your conditions, the entry from "A" is added to a dicitonary object. Since dictionaries can only hold one of each unique item, it automatically gets you the unique count! Then I just display a message box telling you the result.

Please check it out - I hope that I'm not the only one who appreciates how easy VBA is to mangae and alter. BTW, there are 5 unique entries in that list you posted in which I is 3 and C is 11.

Upvotes: 0

Kash
Kash

Reputation: 9039

Multiply the conditions this way:

=SUM(IF(FREQUENCY(A2:A76,A2:A76)>0,(I2:I76=3)*(C2:C76=11)*1,0))

This needs to be an array fomula (Ctrl+Shift+Enter after entering the formula). (I guess you would already know that considering your function in the question returned a value)

Upvotes: 1

Eddy
Eddy

Reputation: 5370

In my experience these type of formulat's tend to get excessively complicated and totally uncomprehensible for others. To create the proper result and keep the whole formula understandable I usually split it up into a couple intermediate columns.

In your case you could:

  • Add a column, say X that has a 1 if I equals 3 otherwise a 0
  • Add a column say Y that has a 1 if C equals 11 otherwise a 0
  • Add a 'total' column that determines if all are true as =X*Y
  • Then do your sumif

This can be extended as far as you like and keeps the whole proces traceable. And if it is of any importance you can always hide the intermediate columns from view

Upvotes: 0

Related Questions