Ir para o conteúdo principal

Como somar valores únicos com base em critérios no Excel?

Por exemplo, eu tenho um intervalo de dados que contém as colunas Nome e Pedido, agora, para somar apenas valores únicos na coluna Pedido com base na coluna Nome, conforme mostrado a seguir. Como resolver essa tarefa com rapidez e facilidade no Excel?

Some valores únicos com base em um ou mais critérios com fórmulas de matriz

Some valores únicos com base em um ou mais critérios com fórmulas de matriz

Para somar apenas valores únicos com base em critérios em outra coluna, use a fórmula abaixo:

1. Copie ou insira a seguinte fórmula de matriz em uma célula em branco:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))

Anote os: Na fórmula acima, A2: A12 são os dados da coluna que contém o valor da condição, D2 é a condição em que você deseja somar os valores, B2: B12 é a coluna que contém os valores que você deseja somar.

2. Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado total e, em seguida, selecione esta célula de fórmula e arraste a alça de preenchimento para baixo até as células para obter todos os valores totais de cada item. Veja a imagem:

Tips: Se você precisar somar todos os valores exclusivos com base em dois critérios, aplique a fórmula abaixo:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

Mais artigos relativos:

  • Soma várias colunas com base em critérios únicos no Excel
  • No Excel, você sempre pode precisar somar várias colunas com base em um critério. Por exemplo, eu tenho uma gama de dados conforme a captura de tela mostrada a seguir, agora, quero obter os valores totais do KTE em três meses - janeiro, fevereiro e março.
  • Vlookup e soma de correspondências em linhas ou colunas no Excel
  • Usar vlookup e a função sum ajuda a descobrir rapidamente os critérios especificados e somar os valores correspondentes ao mesmo tempo. Neste artigo, vamos mostrar dois métodos para vlookup e somar o primeiro ou todos os valores correspondentes em linhas ou colunas no Excel.
  • Valores da soma com base no mês e ano no Excel
  • Se você tiver um intervalo de dados, a coluna A contém algumas datas e a coluna B tem o número de pedidos, agora, você precisa somar os números com base no mês e ano de outra coluna. Neste caso, desejo calcular o total de pedidos de janeiro de 2016 para obter o seguinte resultado. E neste artigo, irei falar sobre alguns truques para resolver esse trabalho no Excel.
  • Soma de valores com base em critérios de texto no Excel
  • No Excel, você já tentou somar valores com base em outra coluna de critérios de texto? Por exemplo, eu tenho um intervalo de dados na planilha conforme a captura de tela a seguir mostrada, agora, eu quero somar todos os números na coluna B correspondentes aos valores de texto na coluna A que atendem a um determinado critério, como somar os números se o as células na coluna A contêm KTE.
  • Valores da soma com base na seleção da lista suspensa no Excel
  • Como mostrado na captura de tela abaixo, você tem uma tabela que contém uma coluna Categoria e uma coluna Quantia e criou uma lista suspensa de validação de dados que contém todas as categorias. Ao selecionar qualquer tipo de categoria na lista suspensa, você deseja somar todos os valores de células correspondentes na Coluna B e preencher o resultado em uma célula especificada. Por exemplo, ao selecionar a categoria CC na lista suspensa, você precisa somar os valores nas células B5 e B8 e obter o número total 40 + 70 = 110. Como conseguir isso? O método neste artigo pode ajudá-lo.

As melhores ferramentas de produtividade para escritório

Kutools for Excel Resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
parte inferior da aba do escritório
Comments (9)
No ratings yet. Be the first to rate!
Pugazh
Pugazh
This comment was minimized by the moderator on the site
I have column "A" listed with various Part nos. Say 50 parts. Each of these 50 parts will be repeated more than once. I column B where in for each of these part nos stock qty say for particular part no 1000 is the qty. This 1000 Qty is shown againt that particular part no in whichever cell it comes in column 'A".

The problem i am facing is sum total of column B is not correct as stock qty is shown n-number of times that particular part finds place in column A.

How do i avoid totaling up this repeated stock qty of one unique part. Say part no P1001 stock is 1000nos. If delivery date of this part P1001 is

Part no..Date.............Qty......Stock
P1001....05-10-22 .....150......1000
P1001....06-10-22......200.....1000
P1001....07-10-22.......250.....1000

in the above table stock is only 1000 but it shows against each delivery date the same stock qty. But when i want to sum the total stock of all part nos it should not take 1000 3 times but only 1 time. How do this?
Reply
0
0
skyyang
skyyang     Pugazh
This comment was minimized by the moderator on the site
Hello, Pugazh
Did you want to sum only the unique stock based on the part number?
Or you can give your problem as a creenshot here, so that, we can understand more clearly.
Thank you!
Reply
Report
0
0
Gus
Gus
This comment was minimized by the moderator on the site
Hi,

I was wondering if there was a formula for three criteria? Or if there's a process/logic to follow for more than two criteria ?

Many thanks,
Gus
Reply
0
0
skyyang
skyyang     Gus
This comment was minimized by the moderator on the site
Hello, Gus,
If there are three criteria of your data, to sum the unique values, please apply the below array formula:
=SUM(IF(FREQUENCY(IF($A$2:$A$10=F2,IF($B$2:$B$10=G2,IF($C$2:$C$10=H2,MATCH($D$2:$D$10,$D$2:$D$10,0)))),ROW($D$2:$D$10)-ROW($C$2)+1),$D$2:$D$10))

After inserting the formula, please press press Ctrl + Shift + Enter keys together to get the result. see below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-sum-unique.png
Please have a try, thank you!
Reply
Report
0
0
Louise
Louise
This comment was minimized by the moderator on the site
Hi,
I would like to sum based on two unique criteria, is this possible?
In the second example, rather than James and October being the specific criteria, James would stay the specific element, and the formula would sum based on the name and the month being unique.
Many thanks
Louise
Reply
0
0
jorge molina
jorge molina
This comment was minimized by the moderator on the site
Hola me podrias ayudar deseo sumar o contar solo valores unicos de fechas especificas me puedes ayudar ya que la formula =SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12)) me sirve pero como esta en ingles no se como colocarla, te agradezco mucho
Reply
0
0
MandyZhou
MandyZhou     jorge molina
This comment was minimized by the moderator on the site
Hello Jorge,

Formula: =SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))

Translate the English version formula into the Spanish version:
=SUMA(SI(FRECUENCIA(SI($A$2:$A$12=E2,SI($B$2:$B$12=F2,COINCIDIR($C$2:$C$12,$C$2:$C$12,0))),FILA($C$2:$C$12)-FILA($C$2)+1),$C$2:$C$12))

Please have a try.

Sincerely,
Mandy
Reply
Report
0
0
kc
kc
This comment was minimized by the moderator on the site
This doesn't actually work as I would have expected. The formula is only counting the unique criteria.

James had 700 units ordered in the first example, yet the total says 600.

The formula won't count his orders that contain the same qty because they are unique. Is there a way to modify this formula so you can get an actual total of all of James' orders?

The second formula has the same issue. James ordered 400 units, not 300 as shown in the summary. 
Reply
0
0
skyyang
skyyang     kc
This comment was minimized by the moderator on the site
Hello, kc, May be the below article can solve your problem, please try:https://www.extendoffice.com/documents/excel/2471-excel-sumif-multiple-criteria.html
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations