Excel: conta instâncias únicas entre duas colunas

Question

Eu tenho uma planilha que tem uma coluna para sites de células e outra coluna de frequência como esta:

SITE      FREQUENCY
MA1084    687
MA1084    687
MA1084    687
MA1084    4382
MA1084    4382
MA1084    4382
MA1096    4358
MA1096    4358
MA1096    4358
MA1096    687
MA1096    687
MA1096    687
MA1096    4382
MA1096    4382
MA1096    4382

Gostaria que uma fórmula adicionasse todas as combinações exclusivas de SITE e FREQUENCY. Minha saída esperada está abaixo. Basicamente eu quero saber quantas frequências únicas cada site celular tem.

SITE      FREQUENCY COUNT
MA1084    687       2
MA1084    687       2
MA1084    687       2
MA1084    4382      2
MA1084    4382      2
MA1084    4382      2
MA1096    4358      3
MA1096    4358      3
MA1096    4358      3
MA1096    687       3
MA1096    687       3
MA1096    687       3
MA1096    4382      3
MA1096    4382      3
MA1096    4382      3

Eu posso fazer isso com uma tabela dinâmica, mas estou procurando uma solução baseada em fórmulas. Qualquer ajuda é apreciada.

    

Answer 1

Uma solução é usar uma coluna auxiliar. Embora nem sempre seja ideal, depende de como você precisa da saída exibida. Supondo que Site e Frequency estejam nas colunas A e B, então, no tipo de coluna de concatenação: =A2&B2 . Em seguida, no tipo de coluna Count : =COUNTIF(C:C,C2) . Então você pega o seguinte:

SITE    FREQUENCY   Concatentae COUNT
MA1084  687 MA1084687   3
MA1084  687 MA1084687   3
MA1084  687 MA1084687   3
MA1084  4382    MA10844382  3
MA1084  4382    MA10844382  3
MA1084  4382    MA10844382  3
MA1096  4358    MA10964358  3
MA1096  4358    MA10964358  3
MA1096  4358    MA10964358  3

    

Answer 2

Você pode usar uma fórmula de matriz ** :

=SUM(IF(FREQUENCY(IF(A$2:A$16=A2,MATCH(B$2:B$16,B$2:B$16,0)),ROW(B$2:B$16)-MIN(ROW(B$2:B$16))+1),1))

Copie conforme necessário.

Ajuste os intervalos para se adequar, mas certifique-se de não torná-los arbitrariamente grandes (e certamente não fazem referência a colunas inteiras!), pois as fórmulas de matriz calculam todas as células transmitidas a elas, incluindo aquelas que estão tecnicamente além do último células usadas no intervalo.

Atenciosamente