Excel - como selecionar colunas com datas para o cálculo da matriz?

Question

não pode envolver minha mente em torno deste. Temos dados em uma planilha com colunas com um cabeçalho de data (junho de 2017, jul de 2017, ago de 2017) e estes são 'cabeçalhos de rolagem' (eles fornecem informações da data atual - agora em março o primeiro mês exibido é abril de 2017 - em Abril o primeiro cabeçalho será maio de 2017. Eu preciso ser capaz de somar o custo nessa coluna com base em um determinado período, como dar o total para um ano completo de dois anos a partir de uma determinada data. Eu tentei o abaixo, mas isso me dá um erro #Value

=SUMIFS(Calc1!EZ2:HR10000,Calc1!EZ1:HR1,">"&DATE((YEAR(Calc1!EX2)+2),1,1),Calc1!EZ1:HR1,"<"&DATE((YEAR(Calc1!EX2)+3),1,1))

Marcá-lo também como uma fórmula de matriz me dá o mesmo resultado.

O que estou fazendo errado e existe uma maneira melhor (usar agregado?).

Obrigado, René

    

Answer 1

Você pode usar a seguinte fórmula:

=sumproduct((--(Calc1!EZ1:HR1>DATE((YEAR(Calc1!EX2)+2),1,1)))*(--(Calc1!EZ1:HR1<DATE((YEAR(Calc1!EX2)+3),1,1)))*(--(Calc1!EZ2:EZ10000<>"")),Calc1!EZ2:HR10000)   

Sumproduct pode somar a referência da matriz (linhas e colunas) para uma condição em linhas de cabeçalho e colunas de cabeçalho, que é o seu caso (soma as células na coluna se o cabeçalho corresponder à condição)
tudo que você precisa fazer é criar um critério para linhas (então a fórmula pode definir o número de linhas), eu acabei de usar:
Calc1!EZ2:EZ10000<>"" você pode criar outra condição para linhas apenas para fazer o sumproduct reconhecer as linhas, já que o condição inicial para Data irá recongnize as colunas, quando as 2 condições forem satisfeitas a célula será adicionada e você terá o resultado. Sumifs funciona quando cada critério tem o mesmo número de linhas que os outros critérios, o que não é o caso de seus critérios serem os cabeçalhos de colunas (Calc1! EZ1: HR1) que é diferente de toda a matriz (Calc1! EZ2: HR10000)

Sua pergunta é como Superusuário question 895987 e esta pergunta tem a resposta que você precisa

    

Answer 2

Hey Rene Gostaria de sugerir alguns exemplos relacionados à sua consulta:

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014") ,

Isto irá somar os valores entre 10 de outubro e 31 de outubro.

=SUMIFS(C2:C9, B2:B9, ">="&B2, B2:B9, "<="&B7)

Acima está a fórmula dinâmica em que, no lugar da data, o endereço da célula foi usado. Espero que isso ajude você.