Fórmula genérica
=SUMPRODUCT(costs,--(range="x"))
Resumo
Para calcular o preço do pacote de produtos usando um "x" simples para incluir ou excluir um produto, você pode usar uma fórmula baseada na função SUMPRODUCT. No exemplo mostrado, a fórmula em D11 é:
=SUMPRODUCT($C$5:$C$9,--(D5:D9="x"))
Explicação
A função SUMPRODUCT multiplica intervalos ou matrizes e retorna a soma dos produtos. Isso parece chato, mas SUMPRODUCT é uma função elegante e versátil, que este exemplo ilustra muito bem.
Neste exemplo, SUMPRODUCT é configurado com dois arrays. A primeira matriz é a faixa que contém o preço do produto:
$C$5:$C$9
Observe que a referência é absoluta para evitar alterações conforme a fórmula é copiada à direita. Este intervalo avalia a seguinte matriz:
(99;69;129;119;49)
A segunda matriz é gerada com esta expressão:
--(D5:D9="x")
O resultado de D5: D9 = "x" é uma matriz de valores TRUE FALSE como este:
(TRUE;TRUE;FALSE;FALSE;FALSE)
O duplo negativo (-) converte esses valores TRUE FALSE em 1s e 0s:
(1;1;0;0;0)
Portanto, dentro do SUMPRODUCT temos:
=SUMPRODUCT((99;69;129;119;49),(1;1;0;0;0))
A função SUMPRODUCT multiplica os itens correspondentes em cada matriz juntos:
=SUMPRODUCT((99;69;0;0;0))
e retorna a soma dos produtos, 168 neste caso.
Efetivamente, a segunda matriz atua como um filtro para os valores da primeira matriz. Zeros na matriz2 cancelam itens na matriz1 e 1s na matriz2 permitem que os valores da matriz1 passem para o resultado final.
Com uma única matriz
SUMPRODUCT está configurado para aceitar vários arrays, mas você pode simplificar um pouco essa fórmula fornecendo um único array no início:
=SUMPRODUCT($C$5:$C$9*(D5:D9="x"))
A operação matemática (multiplicação) força automaticamente os valores TRUE FALSE na segunda expressão para uns e zeros, sem necessidade de um duplo negativo.
