Como posso contar valores únicos em uma coluna com base em critérios em duas outras colunas?

Question

Estou tentando contar o número de entradas exclusivas na coluna A, onde a coluna C diz NÃO e a coluna D diz SIM. No entanto, não consigo criar uma fórmula para fazer isso com base em dois critérios diferentes. Como eu poderia fazer algo assim?

Por exemplo, quero saber o número de países únicos que visualizaram um evento sob demanda, mas não ao vivo, que seria 4 no exemplo a seguir:

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

Nas fórmulas sugeridas na resposta abaixo, consegui que o excel aceitasse a seguinte fórmula, mas isso não retorna um valor. O objetivo disto é inserir um 1 na coluna F se E for igual a 1. Qualquer ajuda seria apreciada:

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

    

Answer 1

Você consideraria adicionar uma fórmula que simplesmente incluísse valores de ambas as colunas e outra que contasse valores novos / exclusivos?

Fórmula para E =$C:$C&$D:$D

Fórmula para F =IF(COUNTIF(E$1:E2,E2)=1,1,0) (este é o exemplo da célula fopr (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

    

Answer 2

Você pode usar a seguinte fórmula de matriz para obter a contagem de registros exclusivos que atendem a todos os critérios. Cole o seguinte na barra de fórmulas e pressione Ctrl + Deslocar + Inserir .

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

É longo e um pouco repetitivo, mas deve funcionar para você. Essa fórmula usa o truque 1/COUNTIF(...) exibido muitos tempos neste site no passado para contar registros únicos. Eu tive que introduzir a condição IF repetitiva para não receber #DIV/0! erros.

O que a fórmula faz:

SUMPRODUCT com um argumento de matriz apenas adiciona os elementos da matriz. Os termos de multiplicação vão para 0 se uma das condições não for atendida e 1 se ambas forem atendidas. A divisão pelo COUNTIFS é uma maneira de dimensionar esse valor para obter a contagem exclusiva. Por exemplo, se houvesse dois registros para GB com On Demand, mas não Live, cada um desses registros seria reduzido para contar como 1/2 de um registro na soma total. Se houvesse três desses registros, cada um contaria como 1/3 . Dessa forma, quando a soma adiciona esses registros, a soma total de GB sob demanda, mas não de Live, será 1, por exemplo, 1/2 + 1/2 = 1 . É assim que a exclusividade é capturada.

    

Answer 3

crie um Makro e nomeie-o como "CountMyUniqueEntries", abra o Editor VBA e copie o seguinte trecho de código no Módulo1:

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

talvez você tenha que ativar o menu "Developer Tools" nas "Opções" primeiro

    

Answer 4

Você pode criar outra coluna com uma fórmula do tipo

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

E então, pegue o número de valores distintos nesta coluna e subtraia 1 dela?

Portanto, se sua condição for verdadeira, o valor real alcançará essa coluna, caso contrário, um valor de marcador de posição será. Você pode então contar os valores distintos

    

Answer 5

isso tende a ser uma fórmula bastante complexa. Eu vou dividir em pedaços, então será mais claro e óbvio como isso funciona.
você já tem 4 colunas A, B, C, D. Você precisará de mais quatro colunas E, F, G, H.

Na coluna E, copie esta fórmula para cada célula (copie usando + sinal no final da primeira célula):

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

Na coluna F copie esta fórmula para a primeira célula:

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

selecione cada célula da coluna F pressione "F2" e depois pressione "Ctrl" + "Shift" + "Enter"

na coluna G copie esta fórmula para cada célula:

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

use uma célula extra, talvez na coluna H e some os 1's de G junto com:

=SUM(G:G)

divirta-se