Olá!
Uma forma de fazer isso que você descreveu (talvez a única sem recorrer ao VBA) é utilizando fórmula matricial utilizando uma técnica que necessita das funções DESLOC, INDIRETO, SUBTOTAL, CONTAR.VALORES e SOMA. A fórmula para sua necessidade está abaixo e ela precisa ser inserida com CTRL+SHIFT+ENTER:

=SOMA(SUBTOTAL(9;DESLOC(D7;LIN(INDIRETO("1:"&CONT.VALORES(D8:D17)));0))*(E8:E17)*(F8:F17<>"X"))/SOMA(SUBTOTAL(9;DESLOC($D$7;LIN(INDIRETO("1:"&CONT.VALORES(D8:D17)));0))*(F8:F17<>"X"))

Veja a fórmula aplicada no seu modelo.

Rafael, excelente. Já tinha tentado com matricial e não tinha conseguido. Muito inteligente utilizar INDIRETO E CONT.VALORES.

Obrigado.