Um problema que surge muito no Excel é contar ou somar com base em várias condições OU. Por exemplo, talvez você precise analisar dados e contar pedidos em Seattle ou Denver, para itens que são Vermelho, Azul ou Verde? Isso pode ser surpreendentemente complicado, então, naturalmente, é um bom desafio!
O desafio
Os dados abaixo representam pedidos, um pedido por linha. Existem três desafios distintos.
Quais fórmulas em F9, G9 e H9 contarão corretamente os pedidos com as seguintes condições:
- F9 - Camiseta ou moletom
- G9 - (Camiseta ou moletom) e (Vermelho, Azul ou Verde)
- H9 - (Camiseta ou moletom) e (Vermelho, Azul ou Verde) e (Denver ou Seattle)
O sombreado verde é aplicado com formatação condicional e indica valores correspondentes para cada conjunto de critérios OR em cada coluna.
Para sua conveniência, os seguintes intervalos nomeados estão disponíveis:
item = B3:
cor B16 = C3:
cidade C16 = D3: D16
A planilha está anexada. Deixe suas respostas abaixo como comentários!
Resposta (clique para expandir)Minha solução usa SUMPRODUCT com ISNUMBER e MATCH assim:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
O que contará os pedidos onde …
- O item é (camiseta ou moletom) e
- A cor é (vermelho, azul ou verde) e
- A cidade é (Denver ou Seattle)
Várias pessoas também sugeriram a mesma abordagem. Gosto dessa estrutura porque é facilmente escalável para lidar com mais critérios e também funciona com referências de células (em vez de valores embutidos em código). Com referências de células, a fórmula em H9 é:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
A chave para esta fórmula é a construção ISNUMBER + MATCH. MATCH é configurado "para trás" - os valores de pesquisa vêm dos dados e os critérios são usados para o array. O resultado é uma matriz de coluna única cada vez que MATCH é usado. Esta matriz contém erros # N / A (sem correspondência) ou números (correspondência), portanto ISNUMBER é usado para converter para os valores booleanos TRUE e FALSE. A operação de multiplicação dos arrays juntos força os valores TRUE FALSE para 1s e 0s, e o array final dentro de SUMPRODUCT contém 1s onde as linhas atendem aos critérios. SUMPRODUCT então soma a matriz e retorna o resultado.
