SOMASE duplo no Excel

Para quem tem o EXCEL 2007, existe uma nova função o SOMASES (com uma letra “s” no fim) que permite mais de uma condição.

Por exemplo, quero somar a coluna “total” (de C3 a C12) apenas para o “joão” e apenas para a data “14/01/2010”. No Excel a partir da versão 2007, isso é possível com esta nova função SOMASES.

tabela excel com 3 colunas

Somase com 2 condições: como fazer?

Para o Excel 2007 ou posterior, teremos:

=SOMASES(C3:C12;A3:A12;”joão”;B3:B12;DATA(2010;1;14))

(Observação: não pude testar, não tenho o Excel 2007)

Bom… Para a maioria dos coitados que como eu só tem o 2003, é possível resolver o problema adicionando uma coluna extra onde os critérios “joão” e data(2010;1;14) sejam usados na fórmula lógica

=E(A3=”joão”;B3=DATA(2010;1;14))

para mostrar os resultados FALSO ou VERDADEIRO. Abaixo, coloquei a fórmula acima na célula E3 e arrastei-a para as outras células abaixo:

coluna extra para o somase Agora posso fazer um SOMASE normal para obter o resultado desejado: =SOMASE(E3:E12;VERDADEIRO;C3:C12).

Outra maneira de resolver o problema (minha solução favorita) é, ao invés de escrever FALSO ou VERDADEIRO, concatenar os nomes e as datas em uma única célula. Fica esquisito, porque para o Excel, uma data é o número de dias a partir de primeiro de janeiro de 1900, ao usar em E3 a fórmula

=A3&B3

ou

=CONCATENAR(A3;B3)

ficaremos com a planilha assim:

planilha com fórmula somaseA fórmula SOMASE usada na planilha acima é:

=SOMASE(E3:E12;”joão40192″;C3:C12)

o número 40192 é o número de dias existentes entre 1/1/1900 e 14/1/2010 incluindo ambos.

Por fim, uma solução mais esquisita ainda, mas que dispensa o uso de uma coluna extra: também é possível resolver o problema por uma fórmula menos conhecida, a fórmula SOMARPRODUTO. Neste caso, basta usar a fórmula:

=SOMARPRODUTO(- -(A3:A12=”joão”);- -(B3:B12=DATA(2010;1;14));- -(C3:C12))

Observe que a função DATA requer que ano, mês e dia sejam digitadas exatamente nesta ordem. Os dois sinais negativos na fórmula são uma maneira de converter resultados numérico para valores lógicos e “enganar” a função SOMARPRODUTO.

Bom, depois de toda essa trabalheira, cada vez acho mais conveniente migrar para o Office 2007…

Dúvidas? é só mandar um e-mail, visite minha página de Informática: www.naotemnome.com/fapa lá tem o meu e-mail… E dicas para Excel, basta olhar no índice!

2 thoughts on “SOMASE duplo no Excel

  1. Na Planilha que remeto uma parte (possui 4188 linhas), preciso somar valores de quantidades cujos itens se repetem ao longo das linhas. cujas variáveis para verificação de igualdade estão nas colunas B e C, ou seja, os códigos representados por números na coluna B e as composições identificadas por seus números constantes da coluna C. Esta necessidade se dá por que na coluna B elas tem a mesma denominação (Composição), somente sendo possível distinguir pelo número a ela atribuido na coluna C. Penso que este critério de verificação da identificação das composições também poderia ocorrer pela coluna D, avaliando texto da descrição.
    Preciso que os valores iguais, por exemplo, código 74164/004 (lastro de pedra britada) tenha seus valores somados em somente uma linha e as demais excluídas, e assim por diante. Muito grato. Já tentei de tudo, CONT.SE, SOMA.SE e outras funções, soma condicional, etc, e nada consegui.
    “ITEM
    Nº” CÓDIGO SINAPI / COMPOSIÇÃO DESCRIÇÃO DO SERVIÇO UNID. QUANT. CUSTO UNITÁRIO “CUSTO
    TOTAL” “%
    ITEM” “%
    TOTAL”
    1 INSTALAÇÕES PROVISÓRIAS
    1.1 73805/001 SINAPI/ março_12 Barracão provisório / fiscalização / escritório (piso em pinho 3a, paredes em compensado 10mm, cobertura em telha amianto 6mm, incluso instalações elétricas e esquadrias) m² 14,52 219,26 3.183,61 10,41% 0,03% 73805/001SINAPI/ março_12 0 3.183,61
    1.2 73805/001 SINAPI/ março_12 Barracão provisório / administração (piso em pinho 3a, paredes em compensado 10mm, cobertura em telha amianto 6mm, incluso instalações elétricas e esquadrias) m² 12,00 219,26 2.631,09 8,61% 0,02% 73805/001SINAPI/ março_12 0 2.631,09
    1.3 73805/001 SINAPI/ março_12 Barracão provisório / almoxarifado (piso em pinho 3a, paredes em compensado 10mm, cobertura em telha amianto 6mm, incluso instalações elétricas e esquadrias) m² 20,00 219,26 4.385,14 14,34% 0,04% 73805/001SINAPI/ março_12 0 4.385,14
    1.4 73805/001 SINAPI/ março_12 Barracão provisório / refeitório (piso em pinho 3a, paredes em compensado 10mm, cobertura em telha amianto 6mm, incluso instalações elétricas e esquadrias) m² 15,00 219,26 3.288,86 10,76% 0,03% 73805/001SINAPI/ março_12 0 3.288,86
    1.5 Composição CPU – 1 Área coberta / argamassa m² 8,00 112,39 899,14 2,94% 0,01% ComposiçãoCPU – 1 0 899,14
    1.6 Composição CPU – 1 Área coberta / serralheria m² 8,00 112,39 899,14 2,94% 0,01% ComposiçãoCPU – 1 0 899,14
    1.7 Composição CPU – 1 Área coberta / carpintaria m² 8,00 112,39 899,14 2,94% 0,01% ComposiçãoCPU – 1 0 899,14
    1.8 Composição CPU – 27 Instalação provisória de água un 1,00 739,38 739,38 2,42% 0,01% ComposiçãoCPU – 27 0 739,38
    1.9 73960/001 SINAPI/ março_12 Instalação provisória de energia un 1,00 1.162,64 1.162,64 3,80% 0,01% 73960/001SINAPI/ março_12 0 1.162,64
    1.10 73752/001 SINAPI/ março_12 Instalação provisória unidade sanitária com 5,00m² un 2,00 2.676,98 5.353,96 17,51% 0,05% 73752/001SINAPI/ março_12 0 5.353,96
    1.11 Composição CPU – 2 Instalação provisória de esgoto un 1,00 1.369,80 1.369,80 4,48% 0,01% ComposiçãoCPU – 2 0 1.369,80
    1.12 74209/001 SINAPI/ março_12 Placa da obra pintada e fixada em estrutura de madeira m² 12,00 274,16 3.289,89 10,76% 0,03% 74209/001SINAPI/ março_12 1 3.289,89
    1.13 74209/001 SINAPI/ março_12 Placa dos autores do projeto, pintada e fixada em estrutura de madeira m² 4,50 274,16 1.233,71 4,04% 0,01% 74209/001SINAPI/ março_12 1 1.233,71
    1.14 74209/001 SINAPI/ março_12 Placa da fiscalização da obra, pintada e fixada em estrutura de madeira m² 4,50 274,16 1.233,71 4,04% 0,01% 74209/001SINAPI/ março_12 1 1.233,71
    Total das Instalações Provisórias 30.569,21 100,00% 0,27% 0,00
    2 TERRAPLENAGEM 30.569,21 0,00
    2.1 73672 SINAPI/ março_12 Limpeza mecanizada de terreno, inclusive retirada de arvore entre 0,05m e 0,15m de diametro m² 23.672,00 0,43 10.223,94 5,92% 0,09% 73672SINAPI/ março_12 1 10.223,94
    2.2 72818 SINAPI/ março_12 Escavacao, carga e transporte de material de 1a categoria, caminho de servico leito natural, com escavadeira hidraulica e caminhao basculante 6 m3, dmt 50 ate 200 m m³ 7.850,00 4,92 38.650,73 22,39% 0,34% 72818SINAPI/ março_12 1 38.650,73
    2.3 Composição CPU – 28 Esc. carga transp. mat 2a cat DMT 50 a 200m c/e m³ 374,00 6,80 2.542,95 1,47% 0,02% ComposiçãoCPU – 28 1 2.542,95
    2.4 Composição CPU – 29 Esc. carga transp. mat 3a cat DMT 50 a 200m m³ 19,00 24,45 464,47 0,27% 0,00% ComposiçãoCPU – 29 1 464,47
    2.5 74152/001 SINAPI/ março_12 Escavação e carga em material de jazida 1ª categoria utilizando trator m³ 3.681,00 4,27 15.716,54 9,10% 0,14% 74152/001SINAPI/ março_12 1 15.716,54
    2.6 72843 SINAPI/ março_12 Transporte comercial com caminhao basculante 6 m3, rodovia pavimentada (material 1ª categoria) t/km 138.994,56 0,54 75.468,49 43,71% 0,66% 72843SINAPI/ março_12 3 75.468,49
    2.7 41721 SINAPI/ março_12 Compactação mecânica a 95% do proctor normal – pavimentação urbana m³ 8.405,00 3,07 25.825,71 14,96% 0,23% 41721SINAPI/ março_12 1 25.825,71
    2.8 41722 SINAPI/ março_12 Compactação mecânica a 100% do proctor normal – pavimentação urbana m³ 767,00 4,91 3.766,98 2,18% 0,03% 41722SINAPI/ março_12 1 3.766,98
    Total da Terraplenagem 172.659,81 100,00% 1,51% 0,00
    3 PAVIMENTAÇÃO 0,00
    3.1 Composição CPU – 30 Regularização do subleito m2 1.990,00 1,90 3.781,72 2,19% 0,03% ComposiçãoCPU – 30 1 3.781,72
    3.2 73710 SINAPI/ março_12 Base de brita graduada m3 298,50 155,98 46.559,31 26,92% 0,41% 73710SINAPI/ março_12 1 46.559,31
    3.3 72843 SINAPI/ março_12 Transporte comercial com caminhão basculante 6 m3, rodovia pavimentada (base de brita) t/km 8.238,60 0,54 4.473,23 2,59% 0,04% 72843SINAPI/ março_12 3 4.473,23
    3.4 73764/005 SINAPI/ março_12 Pavimentação com blocos intertravados de concreto, esp. 8cm, fck = 35mpa, assentados sobre colchão de areia m2 1.990,00 56,53 112.493,78 65,04% 0,98% 73764/005SINAPI/ março_12 1 112.493,78
    3.5 72842 SINAPI/ março_12 Transporte comercial com caminhao basculante 6 m3, rodovia com revestimento primario (areia) t/km 1.432,80 0,65 937,08 0,54% 0,01% 72842SINAPI/ março_12 1 937,08
    3.6 72843 SINAPI/ março_12 Transporte comercial com caminhão basculante 6 m3, rodovia pavimentada (areia) t/km 8.692,32 0,54 4.719,58 2,73% 0,04% 72843SINAPI/ março_12 3 4.719,58
    Total da Pavimentação 172.964,71 100,00% 1,51% 0,00
    4 DRENAGEM 0,00
    4.1 3061 SINAPI/ março_12 Escavação mecânica vala n escor mat 1a cat c/ retroescavadeira ate 1,50m excl esgotamento m³ 159,00 6,44 1.024,20 3,96% 0,01% 3061SINAPI/ março_12 1 1.024,20
    4.2 72915 SINAPI/ março_12 Escavação mecânica de vala em material de 2a. categoria ate 2 m de profundidade com utilização de escavadeira hidráulica m³ 9,00 12,94 116,50 0,45% 0,00% 72915SINAPI/ março_12 1 116,50
    4.3 74015/001 SINAPI/ março_12 Reaterro e compactação mecânica de vala com compactador manual tipo soquete vibratório m³ 133,00 20,64 2.745,76 10,63% 0,02% 74015/001SINAPI/ março_12 1 2.745,76
    4.4 6014 SINAPI/ março_12 Argamassa traco 1:4 (cimento e areia media peneirada), preparo mecânico m³ 0,10 479,40 47,94 0,19% 0,00% 6014SINAPI/ março_12 1 47,94
    4.5 5625 SINAPI/ março_12 Concreto para berço de galeria, inclusive preparo e lançamento m³ 8,00 398,06 3.184,51 12,33% 0,03% 5625SINAPI/ março_12 1 3.184,51
    4.6 74164/004 SINAPI/ março_12 Lastro de brita m³ 3,00 114,93 344,80 1,33% 0,00% 74164/004SINAPI/ março_12 2 344,80
    4.7 73817/002 SINAPI/ março_12 Embasamento de material granular – rachão m³ 21,00 112,18 2.355,84 9,12% 0,02% 73817/002SINAPI/ março_12 1 2.355,84
    4.8 73404 SINAPI/ março_12 Forma madeira 2 vezes pinho 3a esp=2,5cm para peças de concreto armado incl forn materiais e desmoldagem excl escoramento m² 59,00 46,14 2.722,22 10,54% 0,02% 73404SINAPI/ março_12 1 2.722,22
    4.9 7785 SINAPI/ março_12 Tubo concreto simples classe – ps2 pb nbr-8890 dn 400mm para águas pluviais m 147,00 31,70 4.660,11 18,04% 0,04% 7785SINAPI/ março_12 1 4.660,11
    4.10 73724 SINAPI/ março_12 Assentamento de tubos de concreto diâmetro = 400mm, simples ou armado, junta em argamassa 1:3 cimento:areia m 147,00 15,71 2.309,20 8,94% 0,02% 73724SINAPI/ março_12 1 2.309,20
    4.11 Composição CPU – 33 Caixa de ligação e passagem – clp 01 ac/bc un 1,00 1.092,00 1.092,00 4,23% 0,01% ComposiçãoCPU – 33 1 1.092,00
    4.12 Composição CPU – 34 Caixa de ligação e passagem – clp 07 ac/bc un 1,00 1.299,70 1.299,70 5,03% 0,01% ComposiçãoCPU – 34 1 1.299,70
    4.13 Composição CPU – 6 Caixa coletora com grelha de ferro ccg 01 d=0,40m h=1,00m (projeto tipo) un 2,00 643,63 1.287,26 4,98% 0,01% ComposiçãoCPU – 6 1 1.287,26
    4.14 Composição CPU – 7 Caixa coletora com grelha de ferro ccg 02 d=0,40m h=1.50m (projeto tipo) un 3,00 881,95 2.645,86 10,24% 0,02% ComposiçãoCPU – 7 1 2.645,86
    Total da Drenagem 25.835,90 100,00% 0,23% 0,00
    5 SINALIZAÇÃO DEFINITIVA 0,00
    5.1 Composição CPU – 35 Pintura faixa com termoplástico – 3 anos (para aspersão) m² 58,50 37,44 2.190,21 52,40% 0,02% ComposiçãoCPU – 35 1 2.190,21
    5.2 Composição CPU – 36 Pintura setas e zebrado term. – 5 anos (para extrusão) m² 34,00 58,52 1.989,55 47,60% 0,02% ComposiçãoCPU – 36 1 1.989,55
    Total da Sinalização Definitiva 4.179,77 100,00% 0,04% 0,00
    6 BLOCO DE SERVIÇOS DE APOIO (ÁREA: 284,48m²) E CASA DE MÁQUINAS (ÁREA: 12,42m²) 0,00
    6.1 INSTALAÇÕES PRELIMINARES 0,00
    6.1.1 74077/002 SINAPI/ março_12 Locação da obra m² 296,90 2,81 835,33 100,00% 0,01% 74077/002SINAPI/ março_12 1 835,33
    Total das Instalações Preliminares 835,33 100,00% 0,01% 0,00
    6.2 FUNDAÇÕES E ESCAVAÇÃO 0,00
    6.2.1 73481 SINAPI/ março_12 Escavação manual de solo com profundidade máxima de 1,00m m³ 9,30 22,63 210,47 0,78% 0,00% 73481SINAPI/ março_12 1 210,47
    6.2.2 74164/004 SINAPI/ março_12 Lastro de pedra britada m² 0,47 114,93 54,02 0,20% 0,00% 74164/004SINAPI/ março_12 2 54,02
    6.2.3 00039 SINAPI/ março_12 Aço CA60 diâmetro 5,0mm (fornec., corte, dobra e coloc.) Kg 53,80 4,10 220,41 0,81% 0,00% 00039SINAPI/ março_12 2 220,41
    6.2.4 00032 SINAPI/ março_12 Aço CA50 diâmetro 6,3mm (fornec., corte, dobra e coloc.) Kg 46,40 4,05 187,80 0,69% 0,00% 00032SINAPI/ março_12 1 187,80
    6.2.5 00033 SINAPI/ março_12 Aço CA50 diâmetro 8,0mm (fornec., corte, dobra e coloc.) Kg 15,00 3,80 57,01 0,21% 0,00% 00033SINAPI/ março_12 2 57,01
    6.2.6 00034 SINAPI/ março_12 Aço CA50 diâmetro 10,0mm (fornec., corte, dobra e coloc.) Kg 128,50 3,59 461,44 1,70% 0,00% 00034SINAPI/ março_12 2 461,44
    6.2.7 74138/003 SINAPI/ março_12 Concreto estrutural Fck=25 Mpa, com lançamento m³ 5,61 442,59 2.482,91 9,17% 0,02% 74138/003SINAPI/ março_12 1 2.482,91
    6.2.8 73394 SINAPI/ março_12 Forma em madeira comum para fundações, inclusive corte, escoramento, montagem e desforma com reaproveitamento de 2x m² 61,08 30,63 1.870,75 6,91% 0,02% 73394SINAPI/ março_12 2 1.870,75
    6.2.9 74122/001 SINAPI/ março_12 Estaca de concreto 165x165mm – 25tf – profundidade média de 7m + cravação das estacas pré-moldadas de concreto m 266,00 80,91 21.522,96 79,52% 0,19% 74122/001SINAPI/ março_12 1 21.522,96
    Total das Fundações e Escavações 27.067,78 100,00% 0,24% 0,00
    6.3 INFRA-ESTRUTURA 0,00
    6.3.1 73394 SINAPI/ março_12 Forma em madeira comum para fundações, inclusive corte, escoramento, montagem e desforma com reaproveitamento de 2x m² 230,77 30,63 7.068,00 39,01% 0,06% 73394SINAPI/ março_12 2 7.068,00
    6.3.2 00027 SINAPI/ março_12 Aço CA50 diâmetro 16,0mm (fornec., corte, dobra e coloc.) Kg 7,30 3,54 25,85 0,14% 0,00% 00027SINAPI/ março_12 1 25,85
    6.3.3 00034 SINAPI/ março_12 Aço CA50 diâmetro 10,0mm (fornec., corte, dobra e coloc.) Kg 149,90 3,59 538,28 2,97% 0,00% 00034SINAPI/ março_12 2 538,28
    6.3.4 00033 SINAPI/ março_12 Aço CA50 diâmetro 8,0mm (fornec., corte, dobra e coloc.) Kg 321,40 3,80 1.221,55 6,74% 0,01% 00033SINAPI/ março_12 2 1.221,55
    6.3.5 00039 SINAPI/ março_12 Aço CA60 diâmetro 5,0mm (fornec., corte, dobra e coloc.) Kg 322,70 4,10 1.322,06 7,30% 0,01% 00039SINAPI/ março_12 2 1.322,06
    6.3.6 74138/002 SINAPI/ março_12 Concreto estrutural Fck=20 Mpa, com lançamento m³ 19,18 414,22 7.944,68 43,84% 0,07% 74138/002SINAPI/ março_12 1 7.944,68
    Total da Infra-Estrutura 18.120,42 100,00% 0,16% 0,00

    • Infelizmente sua planilha não ficou muito legível no comentário, mas… Já tentou a técnica de criar uma coluna extra e concatenar as colunas B & C nelas? Use o soma.se a partir dessa concatenação…

Comments are closed.