Note: The other languages of the website are Google-translated. Back to English

Como somar com base em critérios de coluna e linha no Excel? 

Eu tenho um intervalo de dados que contém cabeçalhos de linha e coluna, agora, quero obter uma soma das células que atendem aos critérios de cabeçalho de coluna e linha. Por exemplo, para somar as células cujos critérios de coluna são Tom e os critérios de linha são Fev como mostrado a seguir. Neste artigo, vou falar sobre algumas fórmulas úteis para resolvê-lo.

doc soma coluna linha critério 1

Soma as células com base em critérios de coluna e linha com fórmulas


seta azul bolha direita Soma as células com base em critérios de coluna e linha com fórmulas

Aqui, você pode aplicar as seguintes fórmulas para somar as células com base nos critérios de coluna e linha, faça o seguinte:

Insira qualquer uma das fórmulas abaixo em uma célula em branco onde deseja gerar o resultado:

=SUMPRODUCT((A2:A7="Tom")*(B1:J1="Feb")*(B2:J7))

=SUM(IF(B1:J1="Feb",IF(A2:A7="Tom",B2:J7)))

E, em seguida, pressione Shift + Ctrl + Enter juntas para obter o resultado, consulte a captura de tela:

doc soma coluna linha critério 2

Nota: Nas fórmulas acima: Tom e Fev são os critérios de coluna e linha baseados em, A2: A7, B1: J1 são os cabeçalhos das colunas e os cabeçalhos das linhas contêm os critérios, B2: J7 é o intervalo de dados que você deseja somar.


As melhores ferramentas de produtividade para escritório

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

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • 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 sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... 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 ...
  • 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...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2021 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Recursos completos de avaliação gratuita de 30 dias. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • 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
Comentários (15)
Ainda não há classificações. Seja o primeiro a avaliar!
Este comentário foi feito pelo moderador no site
Maravilhoso, este é o que eu estava procurando. Obrigado pela ajuda
Este comentário foi feito pelo moderador no site
Vale ressaltar que das duas fórmulas fornecidas acima não é necessário inserir a fórmula SUMPRODUCT com Ctrl + Shift + Enter. Vai funcionar perfeitamente bem sem ele.
Este comentário foi feito pelo moderador no site
Brilhante
Este comentário foi feito pelo moderador no site
como você faria essa mesma fórmula se quisesse somar fevereiro e março juntos? por favor ajude! obrigado

=SUMPRODUCT((A2:A7="Tom")*(B1:J1="Feb")*(B2:J7))
Este comentário foi feito pelo moderador no site
Olá, Ângela,
Para resolver seu problema, você só precisa aplicar a fórmula abaixo, por favor, tente.

=SUMPRODUCT((A2:A7="Tom")*((B1:J1="Feb")+(B1:J1="Mar"))*(B2:J7)).

Espero que isso possa ajudar-lhe!
Este comentário foi feito pelo moderador no site
Existe uma maneira de fazer isso funcionar com caracteres curinga? Eu gostaria de usá-lo em tudo começando com certos caracteres, mas com (um número fixo de) caracteres indefinidos no final, ou seja =SUM(IF(B1:J1="Fe*",IF(A2:A7=" Para*",B2:J7)))
Este comentário foi feito pelo moderador no site
Se alguém tiver a mesma pergunta, é assim que eu resolvi (no Planilhas Google, não testado no Excel):

=ArrayFormula(SUM(IF(regexmatch(O5:W5,"^Fe."),IF(regexmatch(N6:N11,"^To."),O6:W11))))

Observe que a função IF não suporta caracteres curinga e que para regexmatch os curingas são diferentes e podem ser encontrados aqui: https://github.com/google/re2/blob/master/doc/syntax.txt
Neste caso em particular, usei ^ para indicar que Fe & Tom ocorrem no início do texto e . para permitir qualquer caractere seguinte (* significaria zero ou mais do caractere anterior, por exemplo, Fe* procuraria apenas instâncias com 1 ou mais "e"s após F)
Este comentário foi feito pelo moderador no site
Muito obrigado! Você fez meu dia!! @TeSageDS
Este comentário foi feito pelo moderador no site
Tentei criar uma fórmula para calcular se o D12 está em branco, ele vai calcular do K12 ao AH12 mas parece que essa parte 'regexmatch(D12,"")' não está funcionando
ArrayFormula(SUM(IF(regexmatch(D12,""),K12:AH12)))
Este comentário foi feito pelo moderador no site
No exemplo acima, qual fórmula devo usar se quiser calcular a soma dos valores ganhos por Tom Ruby & Nicol em março?
Este comentário foi feito pelo moderador no site
Olá, Nabar, Para obter o resultado desejado, aplique a fórmula abaixo:=SUMPRODUCT(((A2:A7="Tom")+(A2:A7="Nicol")+(A2:A7="Ruby"))*(B1:J1="Mar")*(B2:J7))
Por favor, tente, espero que possa ajudá-lo!
Este comentário foi feito pelo moderador no site
Olá Skyyang,
Obrigado pela sua resposta. Eu tentei a fórmula que você sugeriu, mas não estou recebendo a resposta certa. Provavelmente não consegui colocar minha consulta corretamente. Deixe-me tentar novamente tenho uma planilha com dados com cálculo diário, numerei a primeira linha 1,2,3,4.....132. 2ª, 3ª, 4ª e 5ª linhas têm títulos de tabela. A primeira coluna tem os números de linha 1, 2,3,.....3005 a partir da linha 6, intervalo B6:EF3005 tem dados preenchidos diariamente. Na segunda planilha, tenho uma tabela na qual quero aplicar uma fórmula que procurará dois números de linha alimentados em duas linhas diferentes e calculará a soma do intervalo B6: EF3005 da planilha de dados entre os dois números de linha de uma determinada coluna viz soma de valores na coluna 15 entre as linhas (linha No's) 50 e 85.
Este comentário foi feito pelo moderador no site
Existe uma maneira de fazer isso, mas em vez de escrever "Tom" e "Feb" na fórmula, você pode fazer referência às células de critérios, por exemplo, A11 e A12?
Este comentário foi feito pelo moderador no site
Olá, Rianna, Sim, como você disse, você pode usar as referências das células para substituir o texto na fórmula, como:
=SUMPRODUCT((A2:A7=A2)*(B1:J1=C1)*(B2:J7))
=SUM(IF(B1:J1=C1,IF(A2:A7=A2,B2:J7)))
Este comentário foi feito pelo moderador no site
Se houvesse negativos na matriz e você quisesse incluir apenas os positivos, o que você faria? 
Tentei fazer algo assim:

=SOMAPRODUTO((Coluna = Critérios)* (Linha = Critérios) * (segunda coluna = Critérios) * (Matriz) * (Matriz >0))

Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL