Olá!
Eu sugiro você utilizar a função SOMARPRODUTO, que diferente de outras funções como CONT.SES e SOMASES, não dependem do arquivo de origem estar aberto para trazer os valores.

No seu caso, a fórmula para contar duplicatas poderia ser:

=SOMARPRODUTO(--(P.xlsm!Tabela1[ENTRADA]=[@DATA]))

e a fórmula para somar valores poderia ser:

=SOMARPRODUTO(--(P.xlsm!Tabela1[ENTRADA]=[@DATA]);P.xlsm!Tabela1[VALOR])

Estas fórmulas devem ser montadas com o arquivo fonte aberto, que depois que for fechado o Excel se encarrega de colocar na fórmula o endereço do arquivo.

Mas tenho uma observação importante com relação a sua base, pois notei que as datas estão escritas com o formato 02.09.2016, que na verdade é interpretada pelo Excel como um texto e não como data. Você poderá ter problemas ao tabular a sua tabela de consolidado mensal. Se estes dados são extraidos de algum sistema, eu sugiro sempre selecionar os dados e mandar substituir ponto (.) por barra (/) para assim os valores foram de fato exergados como data.

ABraços!

Vc pode gerar esses relatórios tanto com tabela dinâmica, Power Query / Nova Consulta quanto com funções e macros...
Mas o mais fácil de montar e manter seria TD e PQ.

Obrigado Issamu

muito boa a ideia.

só tem um porem como faço para deixar a informação gravada na tabela "RELATÓRIO"
EX: se eu fizer hoje a planilha vai der os dados do dia 05/09/2016 dai eu salvo e fecho
manha vou atualizar a planilha dados com as duplicatas do dia 06/09/2016 na linha de baixo e por ai vai....

DATA Nº DUPLICATAS VALOR TOTAL
02.09.2016 15 R$ 1.000.117,80
03.09.2016 14 R$ 7.066.117,70
04.09.2016 7 R$ 2.000.754,80

bom dia DJunqueira

como funciona esse Power Query?

P/ usar o Power Query vc tem de ter o Excel 2013 ou 2016.
Power Query faz as vezes de um banco de dados no Excel onde vc pode gravar as rotinas q vc quer repetir sem digitar código algum. É uma excelente ferramenta.
Ele faria tudo o q vc quer bastando apertar Atualizar tudo. Power Query não atualiza automaticamente como fórmulas.

aqui na minha empresa nao da para baixar o Power Query
o Excel aqui é o 2010 infelizmente

Uma saída bem eficiente também são as conexões com pastas de trabalho. São atualizadas automaticamente ao abrir e ainda se permite a programação de atualizações periódicas. Sua maior vantagem é que não fica pesado e não utiliza nenhuma fórmula, mesmo possibilitando a importação apenas das colunas importantes.

boa tarde Helio Faria

tentei aqui fazer a com pastas de trabalho mas, eu não consegui...