Excel '10 Fórmula para encontrar e somar uma linha com base na interseção de 2 pontos de dados. (ie. Nome (linha), Mês (coluna)

Question

Eu preciso de uma fórmula de matriz do Excel 2010 que leve 2 pontos de dados, digamos um "Nome" (linha) e "Mês" (coluna) e retorne a soma de uma linha com base na interseção desses dois pontos uma tabela de dados. (Peço desculpas, mas não consigo inserir um JPG.) Por exemplo, o intervalo de referência "Nome" da minha tabela é A2: A15, e o intervalo "Mês" correspondente é B1: M1. Os dados contidos na própria tabela seriam vendas mensais pelo nome.

A fórmula necessária retornaria a soma "YTD" de todos os valores (vendas mensais) contidos em uma linha entre a interseção de um "Nome" especificado & "Mês", efetivamente retornando o valor YTD para um determinado "Nome" com base em um "Mês" especificado.

Para minha fórmula (localizada fora da tabela), minha referência de ponto de dados "Nome" é a célula "B20" e minha referência de ponto de dados "Mês" é a célula "C20". (Espero que isso faça sentido !!)

Eu experimentei MUITAS variações de SUM (INDEX (MATCH e SUM) (Fórmulas OFFSET (MATCH), mas sem sucesso. A dificuldade é ter que resolver duas referências de pontos de dados. :(

    

Answer 1

Você pode usar a seguinte fórmula:

SUM(IF(A2:A5=B20,OFFSET(B2,0,0,4,MATCH(C20,B1:M1,0))))

Em seguida, pressione Ctrl + Shift + Enter para informar ao Excel que é uma fórmula de matriz.

    

Answer 2

O que B1: M1 contém exatamente? Valores de texto como "Jan", "Feb" etc. ou datas?

Se forem datas, você pode usar SUMPRODUCT como este

=SUMPRODUCT((A2:A15=B20)*(B1:M1<=C20),B2:M15)

ou para valores de texto, tente

=SUMPRODUCT((A2:A15=B20)*((1&B1:M1)+0<=(1&C20)+0),B2:M15)