Como usar sumif/sumifs com lógica or?

Às vezes precisamos fazer pequenas alterações nas fórmulas para torná-las eficazes.

Simples assim, SUMIF OR. É uma fórmula avançada que ajuda a aumentar a flexibilidade do SUMIF. Sim, você também pode fazer SUMIFS. Mas antes de usá-lo, deixe-me contar uma coisa sobre o SUMIF.

No SUMIF você só pode usar um critério e no SUMIFS você pode usar vários critérios para obter uma soma.

Apenas uma coisa assim. Digamos que em SUMIFS, se você especificar dois critérios diferentes, apenas as células que atenderem a ambos os critérios serão somadas.

Por funcionar com lógica AND, todos os critérios devem ser atendidos para que uma célula seja incluída. Ao combinar a lógica OR com SUMIF/SUMIFS, você pode adicionar valores usando dois critérios diferentes ao mesmo tempo.

Sem mais delongas, vamos aprender esta fórmula incrível.

Nós realmente precisamos da lógica OR? Um exemplo?

Deixe-me mostrar um exemplo onde precisamos adicionar a lógica OR em SUMIFS ou SUMIF.

Dê uma olhada na tabela de dados abaixo onde você tem uma lista de produtos com sua quantidade e status atual de envio.

Nestes dados você tem dois tipos de produtos (defeituosos e danificados) que deverá devolver ao nosso fornecedor.

Mas antes disso, você deseja calcular a quantidade total dos dois tipos de produtos. Se você escolher o método normal, deverá aplicar o SUMIF duas vezes para obter esse total.

Mas se você usar uma condição OR em SUMIF, poderá obter o total de ambos os produtos com uma única fórmula.

OK, vamos aplicar OR com SUMIFS

Antes de começar, baixe este arquivo de amostra aqui   continua.

Em qualquer outra célula da planilha onde deseja calcular o total, insira a fórmula abaixo e pressione Enter.

 =SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))

Na fórmula acima você usou SUMIFS, mas se quiser usar SUMIF, você pode inserir a fórmula abaixo na célula.

 =SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))

Usando as duas fórmulas acima, você obterá 540 no resultado. Para verificar novamente, basta verificar o total manualmente.

Como funciona essa fórmula?

Como eu disse, a função SUMIFS usa a lógica AND para somar os valores. Porém, a fórmula que você usou acima inclui a lógica OR.

Para entender esta fórmula, é necessário dividi-la em três partes diferentes.

1. A primeira coisa é entender que você usou dois critérios diferentes nesta fórmula usando o conceito de array. Saiba mais sobre o conselho aqui .
2. A segunda coisa é que quando você especifica dois valores diferentes usando um array, SUMIFS deve procurar os dois valores separadamente.
3. A terceira coisa é que mesmo depois de usar uma fórmula de matriz, SUMIFS não consegue retornar a soma dos dois valores em uma única célula. É por isso que você precisa juntá-lo à função SUM.

Com o conceito acima, você pode obter o total de ambos os tipos de produtos em uma única célula.

Isso funcionará da mesma forma com SUMIF e SUMIFS.

E você também pode estender sua fórmula especificando o nome do produto no segundo critério em SUMIFS para obter o total de um único produto.

Usando intervalo de critérios dinâmicos

Na seção de comentários, alguém me pediu para usar uma referência de célula para adicionar vários critérios em vez de inseri-los diretamente na fórmula.

Bem, essa é uma pergunta super válida e você pode fazer isso usando um intervalo nomeado dinâmico em vez de valores graves.

E para fazer isso, você só precisa fazer duas pequenas alterações na sua fórmula.

1. A primeira é que em vez de usar colchetes, você deve usar um intervalo nomeado (a melhor maneira é usar um array) do seu valor.
2. E depois disso você precisa inserir esta fórmula usando Ctrl+Shift+Enter como fórmula de matriz adequada.

Então agora sua fórmula será:

Vários critérios com colunas diferentes

Deixe-me levá-lo ao próximo nível desta fórmula. Basta olhar a tabela de dados abaixo.

Nesta tabela você tem duas colunas diferentes com dois tipos de status diferentes. Agora a partir desta tabela de dados você precisa somar a quantidade onde o produto está danificado, o status de devolução é “Não”.

Isto significa apenas produtos danificados e defeituosos que ainda não foram devolvidos. E a fórmula para isso será.

 =SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))

Conclusão

A melhor parte de usar a técnica OR é que você pode adicionar muitos critérios a ela. Se quiser usar apenas a lógica OR, você pode usar SUMIF. E se você deseja criar uma condição OR e uma condição AND em uma única fórmula, você pode usar SUMIFS para isso.

Você sabe, esta é uma das minhas dicas de fórmulas favoritas e espero que você tenha achado útil.

Agora me diga uma coisa. Você já usou esse tipo de fórmula antes? Compartilhe sua opinião comigo na seção de comentários, adoraria ouvir de você, e não se esqueça de compartilhar essa dica com seus amigos.