Parece que você está com problemas porque não entendeu exatamente como funciona a função SUMPRODUCT() .
O que ele faz é multiplicar all os elementos respectivos dos argumentos juntos, e então somar o array resultante.
Veja o seguinte como um exemplo:
=SUMPRODUCT(A1:A3, B1:B3, C1:C3, D1:D3)
Os dois passos para avaliar esta fórmula seriam:
=SUMPRODUCT({A1*B1*C1*D1; A2*B2*C2*D2; A3*B3*C3*D3})
=A1*B1*C1*D1 + A2*B2*C2*D2 + A3*B3*C3*D3
Para o seu caso em particular, o que você realmente precisa fazer é obter a soma de dois sumproducts:
=SUM(SUMPRODUCT(duration1,number1),SUMPRODUCT(duration2,number2))/SUM(number1,number2)
Para sua pasta de trabalho de amostra fornecida, a fórmula em F5 seria:
=SUM(SUMPRODUCT('CHAT US Raw Data'!I6,'CHAT US Raw Data'!P6),SUMPRODUCT('CHAT US Raw Data'!T6,'CHAT US Raw Data'!U6))/SUM('CHAT US Raw Data'!I6,'CHAT US Raw Data'!T6)
Note que isto é equivalente a executar explicitamente as multiplicações dentro da função SUMPRODUCT() e apenas usar a função para somar a matriz resultante (como foi feito na resposta à pergunta da parte 1):
=SUM(SUMPRODUCT(duration1*number1),SUMPRODUCT(duration2*number2))/SUM(number1,number2)
com a fórmula real em F5 sendo:
=SUM(SUMPRODUCT('CHAT US Raw Data'!I6*'CHAT US Raw Data'!P6),SUMPRODUCT('CHAT US Raw Data'!T6*'CHAT US Raw Data'!U6))/SUM('CHAT US Raw Data'!I6,'CHAT US Raw Data'!T6)
Esta fórmula ainda precisa ser adaptada para verificar as datas correspondentes (e os primeiros nomes dos alunos correspondentes, se aplicável), de acordo com suas perguntas anteriores.
Para o dividendo, podemos adicionar um termo de verificação de data extra.
A função divisor SUM() , no entanto, precisa ser modificada antes que uma verificação de data possa ser adicionada. Precisamos de uma soma de duas funções sumif (supondo que você deseja estender a fórmula para várias linhas):
=SUM(SUMPRODUCT((date1=date)*duration1*number1),SUMPRODUCT((date2=date)*duration2*number2))
/SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))
com a fórmula real em F5 sendo:
=SUM(SUMPRODUCT(('CHAT US Raw Data'!H6=E5)*'CHAT US Raw Data'!I6*'CHAT US Raw Data'!P6),SUMPRODUCT(('CHAT US Raw Data'!S6=E5)*'CHAT US Raw Data'!T6*'CHAT US Raw Data'!U6))/SUM(SUMIF('CHAT US Raw Data'!H6,E5,'CHAT US Raw Data'!I6),SUMIF('CHAT US Raw Data'!S6,E5,'CHAT US Raw Data'!T6))
A alternativa para executar manualmente as multiplicações dentro do SUMPRODUCT() (com a coerção booleana implícita resultante), seria coagir as verificações de datas explicitamente para uns e zeros com o operador unário duplo negativo. Por exemplo, (date1=date) se tornaria --(date1=date) :
=SUM(SUMPRODUCT(--(date1=date),duration1,number1),SUMPRODUCT(--(date2=date),duration2,number2))
/SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))
com a fórmula atual:
=SUM(SUMPRODUCT(--('CHAT US Raw Data'!H6=E5),'CHAT US Raw Data'!I6,'CHAT US Raw Data'!P6),SUMPRODUCT(--('CHAT US Raw Data'!S6=E5),'CHAT US Raw Data'!T6,'CHAT US Raw Data'!U6))/SUM(SUMIF('CHAT US Raw Data'!H6,E5,'CHAT US Raw Data'!I6),SUMIF('CHAT US Raw Data'!S6,E5,'CHAT US Raw Data'!T6))
Como nota final, há versões equivalentes do sumproduct para o sumif divisor.
Por exemplo,
SUM(SUMIF(date1,date,number1),SUMIF(date2,date,number2))
é equivalente a
SUM(SUMPRODUCT((date1=date)*number1),SUMPRODUCT((date2=date)*number2))
bem como
SUM(SUMPRODUCT(--(date1=date),number1),SUMPRODUCT(--(date2=date),number2))