SOMASES com critérios acumulativos e não excludentes/SUMIFS with cumulative and not exclusive criteria

Question

Em Português:

É possível, USANDO A FUNÇÃO SOMASES, SOMAR TODOS os VALORES que PERTENÇAM aos GRUPOS "1" OU "97" OU "199" OU "305" OU "410" OU "490" OU "580" OU "715" OU "820", E PERTENÇAM às CATEGORIAS "A" OU "BX" OU "DY" OU "GT" OU "BBE" OU "GAU" OU "NSW", sem ter que usar uma fórmula do tipo =SOMASES(...)+SOMASES(...)+...+SOMASES(...)+SOMASES(...)...?

Se não, como resolvo este problema?

In English:

Is it possible, USING THE FUNCTION SUMIFS, SUM ALL VALUES that BELONG TOthe GROUPS "1" OR "97" OR "199" OR "305" OR "410" OR "490" OR "580" OR "715" OR "820",AND BELONG TO the CATEGORIES "A" OR "BX" OR "DY" OR "GT" OR "BBE" OR "GAU" OR "NSW", without having to use a formula like =SUMIFS(...)+SUMIFS(...)+...+SUMIFS(...)+SUMIFS(...)...?

If not, how do I solve this problem?

Grupo/Group	Categoria/Categorie	Valor/Value
1	A	100
2	B	200
3	C	300
4	D	400
...	...	...
997	ZZV	99700
998	ZZX	99800
999	ZZY	99900
1000	ZZZ	100000

Answer 1

Olá, Rafael!

Obrigado pelo retorno.

Exemplifiquei critérios com quantidades diferentes entre os Grupo e Categorias propositalmente para não influenciar na solução.

Creio que esta solução só funcionaria se a quantidade de critérios de Grupos fosse a mesma também de Categorias, pois a função SOMARPRODUTO só funciona com matrizes de mesmas dimensões, certo?

Neste caso, esta não seria uma solução viável.

Abraços!

Não AshrafHaddad. A estrutura de fórmula que mostrei irá cumprir exatamente a solicitação descrita no enunciado do tópico. O que vai importar na fórmula citada é que as matrizes geradas em cada argumento do SOMARPRODUTO tenham no mesmo tamanho independente da quantidade de critérios definidas em cada uma. A estrutura da fórmula está:

=SOMARPRODUTO(argumento1;Argumento2;Argumento3)

sendo que no argumento 1 e 2 eu vou gerar matriz com valores exclusivos 0 ou 1 e no argumento 3 virá o intervalo de soma. Cada argumento será uma matriz com 999 valores. Dentro do argumento 1 e 2 a soma das matrizes com testes lógicos cumpre o papel equivalente da logica OU.

Se quiser uma solução alternativa, recomento aplicar uma fórmula matricial. Veja dois modelos, que devem ser inseridor com CRTL+SHIFT+ENTER:

=SOMA(SE((CONT.SE('Intervalo com critérios da coluna A';A2:A1000)>0)*(CONT.SE('Intervalo com critérios da coluna B';B2:B1000)>0);C2:C1000;""))

Esta solução acima depende de você ter dois intervalos onde irá digitar os critérios desejados para o grupo e a categoria.

Outra modelo:

=SOMA(SE(--ÉERROS(CORRESP(A2:A1000;{1;97;199;...};0)*(CORRESP(B2:B1000;{"A";"BX";"DY";...};0)));"";C2:C1000))

A fórmula acima você deve indicar os critérios de grupo e categoria dentro das chaves e inserir a fórmula com CRTL+SHIFT+ENTER.

Abraços!

Answer 2

Olá Rafael,

A fórmula =SOMARPRODUTO(Argumento1;Argumento2;Argumento3;Argumento4;Argumento5) teria que funcionar com mais argumentos, sendo Argumento1 (Data), Argumento2 (Empresa), Argumento3 (Grupo), Argumento4 (Categoria) e Argumento5 (Valor), certo?

Estou tentando a partir dos dados abaixo:

Data	Empresa	Grupo	Categoria	Valor
04/08/15	ABC	1	A	100,00
05/08/15	XYZ	2	B	200,00
06/08/15	ABC	3	C	300,00
07/08/15	XYZ	4	D	400,00
...	...	...	...	...
04/08/15	ABC	997	ZZV	99.700,00
05/08/15	XYZ	998	ZZX	99.800,00
06/08/15	ABC	999	ZZY	99.900,00
07/08/15	XYZ	1000	ZZZ	100.000,00

Montar uma tabela de resumo por Data e Empresa, conforme abaixo:

	Coluna G	Coluna H	Coluna I
Linha 1	Empresa	04/08/15	05/08/15
Linha 2	ABC	=SOMARPRODUTO((A2:A1000=H1);(B2:B1000=G2);(C2:C1000=1)+(C2:C1000=97)+...+(C2:C1000=199)+(C2:C1000=820);(D:D1000="A")+(D2:D1000="BX")+...+(D2:D1000="NSW");E2:E1000)	=SOMARPRODUTO((A2:A1000=I1);(B2:B1000=G2);(C2:C1000=1)+(C2:C1000=97)+...+(C2:C1000=199)+(C2:C1000=820);(D:D1000="A")+(D2:D1000="BX")+...+(D2:D1000="NSW");E2:E1000)
Linha 3	XYZ	=SOMARPRODUTO((A2:A1000=H1);(B2:B1000=G3);(C2:C1000=1)+(C2:C1000=97)+...+(C2:C1000=199)+(C2:C1000=820);(D:D1000="A")+(D2:D1000="BX")+...+(D2:D1000="NSW");E2:E1000)	=SOMARPRODUTO((A2:A1000=I1);(B2:B1000=G3);(C2:C1000=1)+(C2:C1000=97)+...+(C2:C1000=199)+(C2:C1000=820);(D:D1000="A")+(D2:D1000="BX")+...+(D2:D1000="NSW");E2:E1000)

Mas, não está funcionando, trazendo a soma igual a ZERO.

Quando eu retino os 2 primeiros argumentos, voltando à fórmula anterior com 3 argumentos, volta a funcionar.

A única diferença em relação à fórmula anterior, é que, invez de comparar com valores contantes como 199 ou "BX", estou, nos 2 primeiros argumentos, comparando com conteúdos de células como H1 ou G2.

Tem ideia do que estou fazendo de errado?

Answer 3

Rafael, boa tarde!

A primeira solução funcionou.

Muito obrigado!

Abraço,

Ashraf

Answer 4

Olá, Rafael!

Obrigado pelo retorno.

Exemplifiquei critérios com quantidades diferentes entre os Grupo e Categorias propositalmente para não influenciar na solução.

Creio que esta solução só funcionaria se a quantidade de critérios de Grupos fosse a mesma também de Categorias, pois a função SOMARPRODUTO só funciona com matrizes de mesmas dimensões, certo?

Neste caso, esta não seria uma solução viável.

Abraços!

Answer 5

Olá AshrafHaddad!

Para fazer este tipo de soma com critérios sem repetir o SOMASES, aplique a função SOMARPRODUTO, conforme modelo abaixo:

=SOMARPRODUTO((A2:A1000=1)+(A2:A1000=97)+...+(A2:A1000=199)+(A2:A1000=820);(B2:B1000="A")+(B2:B1000="BX")+...+(B2:B1000="NSW");C2:C1000)

ou em ingles:

=SUMPRODUCT((A2:A1000=1)+(A2:A1000=97)+...+(A2:A1000=199)+(A2:A1000=820),(B2:B1000="A")+(B2:B1000="BX")+...+(B2:B1000="NSW"),C2:C1000)

Abraços!