Mesclar linhas com a mesma cor, mas valores diferentes

Question

Digamos que eu tenha 4 linhas em 2 colunas como abaixo

      | Col 1 | Col 2 |
Row 1 | Blue  |  54   |
Row 2 | Blue  |  25   |
Row 3 | Red   |  21   |
Row 4 | Blue  |  65   |

O que eu quero é que o Excel reconheça as duas colunas como uma só. Então, quero que o Excel forneça uma saída, que seriam as três colunas a seguir

      | Col 1 | Col 2 | Col 3 |
Row 1 | Blue  |  3    |  144  |
Row 2 | Red   |  1    |  21   |

Onde a primeira coluna deve ser a ‘Variável’. A segunda coluna deve ser o número de vezes que está presente. A terceira coluna deve fornecer a soma dos números próximos à variável. Estarei atualizando os dados, então a fórmula também deve funcionar para novos dados.

Até aqui

Até agora consegui resolver o problema com as duas primeiras colunas. A primeira coluna é simplesmente a variável para que eu possa escrever. A segunda eu fiz usando o COUNTIFcomando. Mas o terceiro é onde estou preso?

Como posso fazer o Excel reconhecer que cada linha possui um determinado valor?

Answer 1

O método a seguir resolverá o problema:

• Insira esta fórmula de matriz em Cell D32e termine comCtrl+Shift+Enter& Preencher.

  {=IFERROR(INDEX($A$32:$A$35,MATCH(0,COUNTIF($D$31:D31,$A$32:$A$35),0)),"")}

• Fórmula na célula E32:

  =IF(COUNTIF($A$32:$A$35,$D32)=0,"",COUNTIF($A$32:$A$35,$D32))

• Fórmula final na célula F32:

=IF(SUMIF($A$32:$A$35,D32,$B$32:$B$35)=0,"",SUMIF($A$32:$A$35,D32,$B$32:$B$35))

Observação

As duas últimas fórmulas foram embrulhadas comSeZeropara substituir Blanksquando não obter valores em linhas sucessivas.

Ajuste as referências de células na fórmula conforme necessário.

Answer 2

Você pode usarSUMPRODUTOfunção para obter o resultado.

Para coluna Contagem:

=SUMPRODUCT(($A$1:$A$4=D1)*1)

Para coluna Soma:

=SUMPRODUCT(($A$1:$A$4=D1)*$B$1:$B$4)