Fórmula genérica
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Resumo
Para somar se as células contêm uma sequência de texto ou outra (ou seja, contêm "gato" ou "rato"), você pode usar a função SUMPRODUCT junto com ISNUMBER + SEARCH ou FIND. No exemplo mostrado, a fórmula na célula F5 é:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
que retorna a soma dos valores em C4: C8 quando as células em B4: B8 contêm "gato" ou "rato".
Explicação
Quando você soma células com critérios "OU", deve ter cuidado para não contar duas vezes quando houver a possibilidade de que ambos os critérios retornem verdadeiros. No exemplo mostrado, queremos somar os valores da coluna C quando as células da coluna B contêm "gato" ou "rato". Não podemos usar SUMIFs com dois critérios, porque SUMIFS é baseado na lógica AND. E se tentarmos usar dois SOMASE (isto é, SOMASE + SOMASE) contaremos duas vezes porque há células que contêm "gato" e "rato".
Em vez disso, usamos uma fórmula como esta:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
O núcleo desta fórmula é baseado na fórmula explicada aqui que localiza o texto dentro de uma célula com ISNUMBER e SEARCH:
ISNUMBER(SEARCH("text",range)
Quando dado um intervalo de células, este snippet retornará uma matriz de valores VERDADEIRO / FALSO, um valor para cada célula do intervalo. Nesta fórmula, usamos este trecho duas vezes, uma para "gato" e outra para "rato", portanto, obteremos duas matrizes. Neste ponto, temos:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Em seguida, adicionamos essas matrizes porque a adição é usada na álgebra booleana para a lógica OR. A operação matemática força automaticamente os valores TRUE e FALSE para 1s e 0s, então terminamos com a matriz abaixo:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Cada número neste array é o resultado da adição de valores TRUE e FALSE nos dois arrays originais juntos. No exemplo mostrado, a matriz se parece com isto:
(2;0;2;1;0)
Precisamos somar esses números, mas não queremos contar duas vezes. Portanto, precisamos garantir que qualquer valor maior que zero seja contado apenas uma vez. Para fazer isso, forçamos todos os valores para TRUE ou FALSE marcando o array com "> 0". Isso retorna TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Que então convertemos para 1/0 usando um negativo duplo (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
e finalmente:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT multiplica os elementos correspondentes das duas matrizes e soma o resultado, retornando 70.
Opção que diferencia maiúsculas de minúsculas
A função SEARCH ignora maiúsculas e minúsculas. Se você precisar de uma opção sensível, substitua SEARCH pela função FIND.
