Como usar uma única célula de referência para alterar uma referência de ano em um ano-mês-data em muitas fórmulas?

Question

Como altero a referência de data na fórmula abaixo: >=2018-1-01 e <=2018-1-31 , para que ela se refira a uma data de célula única na célula J4 corretamente? As fórmulas abaixo funcionam corretamente, sem problemas!

(E8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-1-01",$B$44:$B$84,"<=2018-1-31")

(F8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-2-01",$B$44:$B$84,"<=2018-2-28")

(G8) =SUMIFS(D$44:$D$84,$B$44:$B$84,">=2018-3-01",$B$44:$B$84,"<=2018-3-31")

etc.

A dificuldade é que a cada ano eu tenho que entrar manualmente e mudar cada referência ao ano novo nas fórmulas e, pior, ter que pedir às pessoas em nosso grupo de voluntários que usem o relatório de despesas que não estão familiarizados com o Excel. em fórmulas e editá-las. Não é muito prático na última parte! Na célula J4 se eu puder alterar a data para 2019 no próximo ano e as fórmulas mudarem, bem, todo mundo será capaz de gerenciar isso!

Adicionar uma referência de célula simples não funciona, por exemplo:

=SUMIFS(D$44:$D$84,$B$44:$B$84,">=(J4)-1-01",$B$44:$B$84,"<=(J4)-1-31")  

Outras variações semelhantes a apenas adicionar J4 reference na fórmula acima não funcionam.

Outras variações, como =(DATE(YEAR(J4),MONTH(1),DAY(1))) , funcionam como uma referência independente, mas não na fórmula. (Geralmente retorna 0 .)

A primeira parte da fórmula, =SUMIFS(D$44:$D$84,$B$44:$B$84,) é necessária para procurar as despesas de outra tabela no mês e colocá-las nas colunas mensais de acordo com a captura de tela. Então mudar essa parte da fórmula (que funciona bem) não é uma opção.

Eu tentei todos os tipos de referências de datas na fórmula e, se não receber uma mensagem de erro, simplesmente receberei 0 .

Estou usando o Excel 2011 (mac)

    

Answer 1

Acho que o que você quer é o seguinte:

=SUMIFS(D$44:$D$84,$B$44:$B$84,">="&J4&"-1-01",$B$44:$B$84,"<="&J4&"-1-31")

Em vez de usar (J4)

Ref: link

Eu verifiquei se funciona bem no meu conjunto de dados, veja se isso ajuda você!