Como posso escrever uma fórmula do Excel para fazer cálculos baseados em linha; onde certas condições precisam ser atendidas?

Question

Eu recebo:

Uma planilha do excel contém cerca de 200 tarefas (descritas nas linhas 2-201 na Coluna A). Cada tarefa pode ser elegível para um máximo de dois projetos (existem 4 projetos no total, chamados "P1-P4" - listas suspensas nas colunas B e D); e isso com uma alocação específica de% -rate (as colunas C & E-Column C referem-se à coluna B do projeto, e a coluna E refere-se ao projeto na coluna D). A coluna F mostra a quantidade de dias de trabalho gastos em cada tarefa.

Exemplo na linha 2: Tarefa 1 (Coluna A); P1 (Coluna B); 80% (coluna C); P3 (coluna D); 20% (coluna E); 3 (coluna F)

Eu preciso saber a soma dos dias úteis gastos no Projeto P3, respeitando a porcentagem de elegibilidade. Eu sei como calcular isso para cada tarefa (cada linha) - por exemplo, para a Tarefa 1: = SE (B2="P3"; C2 * F2) + SE (D2="P3"; E2 * F2)

No entanto, em vez de repetir isso para cada tarefa, eu preciso de uma fórmula que os acrescente todos juntos. Infelizmente, a fórmula a seguir mostra um erro: = SE (B2: B201="P3"; C2: C201 * F2: F201) + SE (D2: D201="P3"; E2: E201 * F2: F201)

Alguém pode ajudar por favor? Obrigado !!

    

Answer 1

Você pode usar SUMPRODUCT() neste caso para fazer o que quiser ... multiplicar o% pelas horas para projetos correspondentes. A seguir, uma fórmula:

=SUMPRODUCT(--(B2:B201="P3"),C2:C201,F2:F201) 
        + SUMPRODUCT(--(D2:D201="P3"),E2:E201,F2:F201)

Essencialmente você está checando B contra "P1" e se for igual a, é 1, se falhar é 0, então multiplique por% em C e os dias em F.

Aqui está uma ótima referência para a fórmula SUMPRODUCT e os truques que você pode fazer com ela: link