Contar e Somar Condicional utilizando Somarproduto Excel

Planilha de Controle de Horas de Terceiros - Excel
Planilha de Controle de Horas de Terceiros – Excel
26 de agosto de 2012
Reexibir todas as planilhas e Ocultar as planilhas novamente  - Excel VBA
Reexibir todas as planilhas e Ocultar as planilhas novamente – Excel VBA
31 de agosto de 2012

Contar e Somar Condicional utilizando Somarproduto Excel

Objetivo: Demonstrar a utilização da função Somarproduto na contagem e soma condicional de valores, com a vantagem de não ser necessário abrir a planilha base para atualizar os dados.

Contribuição: Este artigo foi enviado pelo amigo Samuel Suzini, ao qual agradeço pela contribuição.

 A função somarproduto no Excel realiza uma ação de multiplicação entre duas listas de dados.

Estas listas de dados tem que ter o mesmo tamanho e podem ser condicionadas a algum valor como é o caso do nosso exemplo aonde são somadas as quantidades vendidas por linha e vendedor e também o valor total do faturamento de cada venda por linha e vendedor.

Estas ações podem ser feitas também com o uso do cont.se e do somase, porém com a diferença de que se você estiver buscando os dados de outro arquivo, não será necessário abrir este outro arquivo, o que seria o caso na utilização destas duas funções.

Função de contagem condicional excel

1. A função utilizada na contagem foi =SOMARPRODUTO((GERAL!$B$2:$B$199=RESUMO!B2)*(GERAL!$D$2:$D$199=RESUMO!E2)) aonde temos:

        a. GERAL!$B$2:$B$199 -> matriz dos vendedores que efetuaram cada venda;

        b. =RESUMO!B2 -> esta cláusula na função faz com que sejam retornados da lista GERAL!$B$2:$B$199 apenas os valores que sejam iguais ao código do vendedor que consta nesta célul;

        c. * -> a multiplicação determina que os dados cujo o resultado da comparação seja alcançado sejam multiplicados pela quantidade na segunda comparação;

        d. GERAL!$D$2:$D$199=RESUMO!E2 -> Retorna o resultado da coluna D da planilha Geral aonde os dados sejam dos departamentos constantes em Resumo!E2.

Função de soma condicional excel

1. Na função de soma condicional utilizada foi =SOMARPRODUTO((GERAL!$B$2:$B$199=RESUMO!B2)*(GERAL!$D$2:$D$199=RESUMO!E2)*(GERAL!$C$2:$C$199)) aonde temos as mesmas condições anteriores, com a diferença da última parte aonde multiplica-se os itens que atendem os critérios pela coluna de valor da venda.

        a. GERAL!$B$2:$B$199 -> matriz dos vendedores que efetuaram cada venda;

        b. =RESUMO!B2 -> esta cláusula na função faz com que sejam retornados da lista GERAL!$B$2:$B$199 apenas os valores que sejam iguais ao código do vendedor que consta nesta célul;

        c. * -> a multiplicação determina que os dados cujo o resultado da comparação seja alcançado sejam multiplicados pela quantidade na segunda comparação;

        d. GERAL!$D$2:$D$199=RESUMO!E2 -> Retorna o resultado da coluna D da planilha Geral aonde os dados sejam dos departamentos constantes em Resumo!E2.

        e. *(GERAL!$C$2:$C$199))  -> Esta última parte realiza a multiplicação dos valores que atendem os critérios pelo valor de faturamento de cada venda.

Abaixo você pode baixar o arquivo enviado pelo Samuel.

GUT PPT

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

5 Comentários

  1. Mauro disse:

    Tenho duas planilhas, sendo um com o estoque anterior, outra com as saídas do dia e quero o resultado em outra com estoque atual, fazendo a subtração entre estoque anterior com a saída…em ambas tem código de produtos, porém nem todos os códigos tem na saída..

  2. Ruben disse:

    Muito obrigado pela publicação.
    Utilizei para cálculo de média ponderada de valores por mês.

  3. Edevaldo Nogueira disse:

    Bom dia,
    Qual a formula consigo colocar para uma Media ponderada com condicional, pois preciso fazer uma média ponderada de preço separando os contratos de compra e Venda, para saber a margem média de lucro. O relatório sai junto os contratos de Compra e Venda.

    • Marcos Rieper disse:

      Depende da versão do seu Excel há a função MÉDIASE e a MÉDIASES, pode usar elas se fizer uma coluna auxiliar, ou senão, use as funções SOMASE e CONT.SE dividindo a soma pela quantidade.

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.