2

I am trying to to count the number of unique entries in column A where Column C says NO and column D says YES. However, I cannot devise a formula to do this based on two different criteria. How could I do something like this?

For instance, I want to know the number of unique countries that have viewed an event on-demand but not live, which would be 4 in the following example:

country  |  Preview  |  Live  |  On Demand
GB       |  NO       |  NO    |  YES
GB       |  NO       |  YES   |  YES
ES       |  NO       |  YES   |  YES
DE       |  NO       |  NO    |  YES
FR       |  NO       |  NO    |  YES
US       |  NO       |  NO    |  YES

From the formulas suggested in the answer below I have managed to get excel to accept the following formula but this does not return a value. The aim of this is to enter a 1 in column F if E is equal to 1. Any help would be appreciated:

=IF(E=1, IF(FREQUENCY(MATCH(A2:A214, A2:A214, 0), MATCH(A2:A214, A2:A214, 0)) > 0, 1, 0))
Excellll
  • 12,847
Andrew
  • 31

5 Answers5

1

Would you consider adding a formula that simply comonbines values of both columns, and another that counts new/unique values?

Formula for E =$C:$C&$D:$D

Formula for F =IF(COUNTIF(E$1:E2,E2)=1,1,0) ( this is example fopr cell( F2)

country  |  Preview  |  Live  |  On Demand | Combined | New/unique
GB       |  NO       |  NO    |  YES       | NOYES    | 1
GB       |  NO       |  YES   |  YES       | YESYES   | 1
ES       |  NO       |  YES   |  YES       | YESYES   | 0
DE       |  NO       |  NO    |  YES       | NOYES    | 0
FR       |  NO       |  NO    |  YES       | NOYES    | 0
US       |  NO       |  NO    |  YES       | NOYES    | 0
sgp667
  • 751
1

You can use the following array formula to get the count of unique records meeting all criteria. Paste the following into the formula bar and press Ctrl+Shift+Enter.

=SUMPRODUCT((C2:C8="NO")*(D2:D8="YES")/IF(COUNTIFS(A2:A8,A2:A8,C2:C8,"NO",D2:D8,"YES")=0,1,COUNTIFS(A2:A8,A2:A8&"",C2:C8,"NO",D2:D8,"YES")))

It's long and a little repetitive, but it should work for you. This formula uses the 1/COUNTIF(...) trick that's shown up many times on this site in the past for counting unique records. I had to introduce the repetitive IF condition to keep from getting #DIV/0! errors.

What the formula does:

SUMPRODUCT with one array argument just adds the elements of the array. The multiplication terms go to 0 if either condition is not met, and 1 if they are both met. The division by the COUNTIFS is a way of scaling this value to get the unique count. For instance, if there were two records for GB with On Demand but not Live, each of those records would get scaled down to count as 1/2 of a record in the total sum. If there were three such records, each would count as 1/3. That way when the sum adds these records, the total sum for GB On Demand but not Live will be 1, e.g. 1/2+1/2=1. This is how uniqueness is captured.

Community
  • 1
Excellll
  • 12,847
0

create a Makro and name it "CountMyUniqueEntries" open the VBA-Editor and copy the following code snippet into Module1: 

Sub CountMyUniqueEntries()
    Dim cells As Range: Set cells = Excel.Selection
    'the selection does only contain data, no column-headers
    Dim i
    Dim uniqueCountries As New Collection
    For i = 1 To cells.Rows.Count
        Dim Live As Boolean: Live = (UCase(cells(i, 3)) = "YES")
        Dim OnDemand As Boolean: OnDemand = (UCase(cells(i, 4)) = "YES")
        Dim country As String: country = UCase(cells(i, 1))
        If Not Live And OnDemand Then
            If Not Contains(uniqueCountries, country) Then
                uniqueCountries.Add country, country
            End If
        End If
    Next
    MsgBox "The number of unique countries is: " & uniqueCountries.Count 
End Sub
Private Function Contains(col As Collection, ByVal entry As String) As Boolean
    On Error Resume Next
    If IsEmpty(col(entry)) Then: 'DoNothing
    Contains = (Err.Number = 0)
    On Error GoTo 0
End Function

maybe you have to activate the menu "Developer-Tools" in the "Options" first

OlimilOops
  • 376
0

Can you create another column with a formula of the sort

IF(<Condition>,Actual Value,Placeholder Value)

And then, take the number of distinct values in this column and subtract 1 from it?

So, if your condition is true, the actual value reaches this column, else a placeholder value does. You can then take a count of the distinct values

Akash
  • 3,758
  • 1
  • 21
  • 34
0

this tends to be a rather complex formula. I will split it into pieces, so it will be more clear and obvious how this works.
you have already 4 columns A, B, C, D. You will need four more columns E, F, G, H.

In column E copy this formula to every cell (copy using +sign right bottom of first cell): 

=IF(C2="NO", IF(D2="YES", 1, 0), 0)

In column F copy this formula to the first cell: 

=IF(FREQUENCY(MATCH(A2:A8, A2:A8, 0), MATCH(A2:A8, A2:A8, 0)) > 0, 1, 0)

select every cell of column F press "F2" and after that press "Ctrl"+"Shift"+"Enter"

in column G copy this formula to every cell: 

=IF(E2=1, IF(F2=1, 1, 0), 0)

use an extra cell, maybe in column H and sum the 1's of G together with: 

=SUM(G:G)

have fun

OlimilOops
  • 376