Usando o Sumproduct no array de diferenças em folhas separadas

Question

Desculpe título provavelmente não faz sentido .. mas aqui está o que estou tentando fazer. Eu tenho uma lista de projetos em uma planilha com o número total de horas atuais por projeto por pessoa. Eu tenho outra folha com taxas (por pessoa, para diferentes anos). Então, eu tenho uma folha do total de horas do projeto em 31 de dezembro de vários anos. Assim, a folha de 2017 incluirá todos os anos até 17 de 2016, todos os anos até 2016, etc. Agora eu estou tentando fazer o ano de 2017 e posso construir isso para incluir outros anos, conforme necessário. Aqui está o que eu tenho e vou tentar explicar:

  =SUMPRODUCT('rates'!B3:'rates'!J3 ,X8-'2017Projects'!B2:AF8-'2017Projects'!J2) + SUMPRODUCT('rates'!B2:'rates'!J2:,'2017Projects'!B2:'2017Projects'!J2)

Então, o que estou tentando fazer é pegar as taxas de 2017 (taxas B3-J3) e fazer um somatório das horas atuais menos as horas do ano anterior (horas atuais em X8 - 2017Projetos! B2 para células AE8 atual - '2017Projects'! L2 ') para aplicar a taxa anual a essas horas. Então, para capturar o total aqui eu só faço um sumproduct das horas de 2017 até hoje usando a taxa avg, mas o que eu gostaria de fazer é similarmente sumproduct a diferença das 17/16 horas, depois 16/15 horas, etc Parece que isso não funciona como esperado e você não pode usar a soma dentro de um produto final. Eu vi o SUMIF, mas não consigo fazer funcionar como eu quero. Alguma sugestão para um produto final usando a diferença de 2 matrizes? Obrigado!

EDIT: btw, eu quero que isso vá em uma coluna de custo total após as horas de projetos, não incluiu isso na amostra ..

Os cálculos que estou tentando fazer são os seguintes:

Sum of 
[
    rate b3 * (current hours X8 - 2017Projects B3) [171 * (3.5 - 0)] 
    rate c3 * (current hours Y8 - 2017Projects C3) [214 * (7.5 - 5.25)] 
    rate d3 * (current hours Z8 - 2017projects D3)
    ..etc
]

link para o arquivo de amostra: link

    

Answer 1

Em vez de SUMPRODUCT , acho que uma fórmula de matriz é o que você precisa.

Se eu entendi corretamente, você tem esses arrays nos quais deseja realizar operações matemáticas:

'rates'!B3:J3 2017 Preços

X8:AF8 Horas do ano atual (acumulado até a data com anos anteriores)

'2017Projects'!B3:J3 2017 horas

E o que você quer é 2017 Rates * ( Current Year hours - 2017 hours ) para cada elemento e, em seguida, a soma de todos.

=SUM('rates'!B3:J3*(X8:AF8-'2017Projects'!B3:J3))

Esta é uma fórmula de matriz. Digite-o e pressione Ctrl-Shft-Enter. Sua fórmula agora deve estar entre colchetes {} .

Isso subtrai a matriz '2017Projects'!B3:J3 da matriz X8:AF8 , um elemento por vez, resultando em uma nova matriz com os resultados dessa operação, o que significa que o primeiro elemento dessa nova matriz será igual a X8 - '2017Projects'!B3 , o segundo elemento será Y8 - '2017Projects'!C3 e assim por diante. Essa matriz é então multiplicada, um elemento por vez, com a matriz 'rates'!B3:J3 . A matriz resultante disso é então somada.

Meu sistema está bloqueado em seus sites para que eu não consiga seus arquivos, mas deixe-me saber se perdi alguma parte importante.